Selecting Data Seperated By Commas

Jun 3, 2008

Have a column in my DB called _Venue, which has been populated with address information separated by commas, like this:

Address1,address2,address3,address4

The address has sometimes only a few lines, for example I may have just Address1 or just address1 and address2 filled etc.

I need to write a query that will individually select addressX, so I can pick and choose what part of the full address to use.

Can these be done?

Thanks in advance.

View 9 Replies


ADVERTISEMENT

How To Split Names Seperated By Commas And Rejoin Them...

Mar 11, 2008

I use SQL Server 2000.

I have some names stored in a table in the following format...

eg
"Royales, Jon"
"Smith, Alan"
"Jones, Tom"

what I would like to see is...

"Jon Royales"
"Alan Smith"
"Tom Jones"

I assume I should search the string for a comma (,) and use split and then rejoin the values. I need to do this as part of my select statement... is it possible?

I have searched the forums for similar posts but didn't find anything helpful.

Jon

View 11 Replies View Related

Ordeering Data Separated By Commas

Jan 1, 2008

Hey everyone. I've never posted on this site before so if I miss anything, please let me know.

I need to order the following data

18
14,15
13.2
14.2
15,16
15
0
14.1
12
6
15,16,18
11
16
15,17
13
14
14,15.2
17

into this order:

0
6
11
12
13
13.2
14
14.1
14.2
14,15
14,15.2
15
15,16
15,16,18
15,17
16
17
18

Because the data contains commas, I've had to change the data type to varchar. I've seen several instances of PATINDEX used however I have not ran across any of them that would put the above in the order in which I need. Btw, I have about 20 columns with this type of data and I eventually plan to use the results of each column to populate dropdownlists . Thank you all in advance.

View 11 Replies View Related

Some Problem In Data Flow While Upload A || Seperated Text File

Jul 25, 2006

I am facing one problem with sql 2005 SSIS while uploading a "||" seperated text file with Data Flow in SSIS.Below I am giving the sample data of the text file

data||data||data

data||data||data

When I uploaded it using sql 2000 DTS it was not giving and it was being uploaded into the table but when I tried with SQL 2005 it is giving the below problem.

"[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". "



View 10 Replies View Related

How To Display Money Type Data With Commas Every 3 Digits?

Oct 4, 2006

How to display money type data with commas every 3 digits?

I tried :

CONVERT (money, ProjectCost, 1) AS ProjectCost

but it gives me the type as 1234.56 no commas in it?

can anyone help me with that?

thanks!

View 4 Replies View Related

Format Money MS SQL Field Data As Number With Commas And No Decimals

Mar 20, 2006

If I pull a value from a MSSQL field with is defined as money, how can I get it to display in a textbox with commas and NO decimals?
87000.0000 = 87,000
I can currently remove the decimals like below but is there a way to add the commas as well?
decRevenue = drMyData("Revenue")
txtRevenue.Text = decRevenue.ToString("f0")
It current shows "87000".
 
 

View 1 Replies View Related

Integration Services :: How To Deal With Commas In Data When Exporting To CSV File

Mar 9, 2011

I have a table that I am exporting via a SQL server table to a csv file, via the flat file connection manager within SSIS. My issue is that one of the fields in my table contains "," (commas), so when the file is created it creates new columns in the csv file because the field is "," comma delimited on the column. Is there anyway to get round this without having to get rid of commas in my table (which I've tried and works)?

View 12 Replies View Related

Selecting A Single Data Source From Multiple Data Sources

Aug 4, 2006

Hi,

I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.

So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.

I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.

My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.

Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)

I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).

The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.

I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.

Many thanks

Rob Gibson

View 5 Replies View Related

Data Access :: Selecting Data Without Making Any Lock

Oct 26, 2015

I have a scenarios where if I execute select * from tbl it should display the all the records without waiting to release any lock.  For example suppose I have one table called tbl and one session is updating one records and not committed yet and in other session if i execute select * from table I can see all the records, for that records it can show me older version. but my select statement should not wait to commit another session and also it should not show the modified data.

View 4 Replies View Related

SQL Comma Seperated List

Jul 26, 2005

Is there a way to return a comma seperated list in a  query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.

View 4 Replies View Related

Comma Seperated Values

May 5, 2008

What is the best way? I have a field of comma seperated article type id values in a users profile. This list is a set of values that sows the article types the person does NOT want to see. Each article has an article type id. I need to do a select joining the article table to the member table that only shows those article id's that are not in the comma seperated list. How would I do that in a sql statement?

View 3 Replies View Related

Comma Seperated Select

May 14, 2008

Not sure the best way to do this. They are passing in a comma seperated string. I have a field that has comma seperated strings in it. I need to compare the 2 strings and do a select on that field that has any of the words in the string passed in. So if the pass...
"car,boat,van"

and in the field 2 of the 3 records get sent back
car,train,bike
boat,truck,plane
bike,plane,train

I would send them back the top 2 because the top one contains car and the second contains boat

View 5 Replies View Related

Selecting Data

Aug 21, 2001

Hello .
Sometimes when i try to select data from a table (from a selected database)
using SQL Query Analyzer (for exemple) i receive an error message : "The Object XX does not exist" . But when i prefix it by the Owner it work .

What is the problem then ?

View 1 Replies View Related

Selecting The Most Recently Edited Item AND Selecting A Certain Type If Another Doesn't Exist

Sep 20, 2007

I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.

I don't need this for an individual ID select, I need it applied to every record from the table.

My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]

[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]

AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.

How can I do this efficiently with perfomance in mind???

Thank you in advance for any and all replies...

View 2 Replies View Related

Storing Comma Seperated Values

Mar 7, 2008

Hi I have a text field called testing which shows the selection the user has made from a checked list. I want to save the contents of testing to a datadase with coloum varchar and with a comma to seperate the selections. It stores ok but sql queries do not recognise them as comma seperated values. e.g the selection from the vb code produses: Beauty, Fashion, Travel. Sql takes the lot as one. When I test a query say select from table type where Category = beauy it gives nothing. How do I store it so sql would recognise it as seperate enteries.
my vb code is below
Dim msg As StringDim li As ListItem
msg = ""For Each li In CaregoryCheckBoxList.Items
If li.Selected = True Then
'asp has a security default of no accetping html in input fields br is html code so causes error
'msg = msg & "<br>" & li.Text & " selected."
msg = msg & li.Text & "," & " "
End If
Next
Testing.Text = msg

View 10 Replies View Related

Storeprocedure Having Two Variables Having , Seperated Values

Mar 15, 2008

can u give me some idea how to make Sp who having two variables as a parameter having values seperated by ","
now thses vaues have to insert in to two tables tbColor .... colorname,product_id
                                                           and   tbSize.......sizename,product_id
thanksss 

View 11 Replies View Related

OLTP And Reporting Databases Seperated?

Jan 12, 2006

we are using an object database for our OLTP but for reporting we have
got some issues about performance as the cpu becames a bottleneck.And
we want to be able to run on low end computers...

One of our team members suggested to replicate the object database to a
SQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?
   
    *we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

View 2 Replies View Related

Script For Comma Seperated Values

Jul 23, 2005

To get rid of redundant data in a table, my cleint will be providingsomething like this:IDtokeep Ids to delete34 24,35,4912 14,178,145754 32,65,68I have to write a script for each of the above rows which looks likethis:-----------------------------------update sometableset id = 34where id in (24,35,49)delete from sometablewhere id in (24,35,49)-----------------------------------As I said I have to do this for EACH row. Can I somehow automate thisor will I need to write to same script for each row (there are about5000 rows in this audit table)Any help is highly appreciated.Here is the DDL and inserts for the audit table.IF object_id(N'dbo.dataclean','U') is not nullDROP TABLE [dbo].[dataclean]GOCREATE TABLE [dataclean] ([IdTokeep] int NULL ,[IdsTodelete] varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )GOINSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(34,'24,35,49')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(12,'14,178,1457')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(54,'32,65,68')GO

View 3 Replies View Related

Passing Comma Seperated Parameter To SP

Jan 21, 2008

Hi,

My SP is,

ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber nvarchar(30),@StoreId nvarchar(500),@Year int)

AS

DECLARE @SQL nvarchar(1000)

-- Searching ItemNumber in Inventory_SKU

DECLARE @Count int

DECLARE @ItemNumSKU varchar(50)

SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId1) AND ItemNum = @ItemNumber1'

EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@Count12=@Count OUTPUT,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber

IF (@Count = 0)

BEGIN

SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId1) AND AltSKU = @ItemNumber1'

EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@ItemNumSKU1=@ItemNumSKU OUTPUT

SET @ItemNumber = @ItemNumSKU

END

-- Creating table variable to have values from 1 to 12

CREATE TABLE #MonthSales (MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)

DECLARE @Cnt INT

SET @Cnt = 1

WHILE(@Cnt <= 12)

BEGIN

INSERT INTO #MonthSales VALUES(@Cnt,0,0,0)

SET @Cnt = @Cnt + 1

END

--Joining query result with the table variable to get required result

DECLARE @Status1 Char(1)

SET @Status1 = 'C'

-- putting ' for comma seperated storeid

SET @SQL = N'SELECT'

SET @SQL = @SQL + N' MS.MonthNumber,'

SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'

SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'

SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'

SET @SQL = @SQL + N' FROM'

SET @SQL = @SQL + N' (SELECT '

SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'

SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'

SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'

SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '

SET @SQL = @SQL + N' FROM '

SET @SQL = @SQL + N' Invoice_Totals '

SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '

SET @SQL = @SQL + N' WHERE '

SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId1) '

SET @SQL = @SQL + N' AND ItemNum = @ItemNumber1'

SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year1'

SET @SQL = @SQL + N' GROUP BY'

SET @SQL = @SQL + N' DATEPART(mm, DateTime)'

SET @SQL = @SQL + N' ) Temp '

SET @SQL = @SQL + N' RIGHT OUTER JOIN #MonthSales MS ON MS.MonthNumber = Temp.Month#'

SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'

EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30),@Year1 int',@Status = @Status1,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@Year1=@Year


It is working If I am passing sigle storeId to the above SP. If I am selecting multiple StoreIDs from the report. It is not returning correct value. Can you please give me a suggestion?

Thanks

View 3 Replies View Related

Selecting Data From 3 Tables...

Feb 12, 2006

Plugging away at my little message board that I am working on, I have hit another SQL snag. I am trying to select a list of all posts in a thread. I have to get each post, the name of the poster (which comes from a different table - referenced by userid), as well as some profil information, from a 3rd table (also referenced by the userid).

View 2 Replies View Related

Selecting And Inserting Data Into Another DB

Jan 28, 2004

Iam farely new to this so sorry for the newbie question.. but how do you select all data from one table and insert that data into the same table but in another sql database.

View 5 Replies View Related

Selecting Parts Of Data

Apr 20, 2008

Hey guys,

I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.

Any ideas how this can be achieved?

Thanks.

View 8 Replies View Related

Selecting Data From Datalist

Jan 19, 2007



Hi all

I am having problem in selecting the data from my datalist. Here is teh sample code
<asp:datalist id="dlAttrName" runat="server" OnItemCommand="dlAttrName_SelectData" DataKeyField="attrID">
<ItemTemplate>
<asp:LinkButton ID="lbSelect" Runat="server" CommandName="Select" style="display:none">Select</asp:LinkButton>
<%# Container.DataItem("attrDef1")%>
</ItemTemplate>
</asp:DataList>
Private Sub dlAttrName_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlAttrName.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
'Add eventhandlers for highlighting
'a DataListItem when the mouse hovers over it.
e.Item.Attributes.Add("onmouseover","????")
e.Item.Attributes.Add("onmouseout", "????")
'Add eventhandler for simulating
'a click on the 'SelectButton'
e.Item.Attributes.Add("onclick", ?????????????????????, String.Empty))
??????????????????? this should have something like this
Me.Page.ClientScript.GetPostBackEventReference(e.Item.Controls(1)
But I am not able to get the ClientScript option, I don't know which namespace should I import?

I don't know whether I am right or not.
Its urgent please reply back soon,any help will be useful.
Thanks!!!!!!!

View 1 Replies View Related

Query Using Commas

Mar 12, 2007

Hi,
I have table Article(ID,Title,FAID)
I need a query that will select all the Article.ID records where the FAID contains the number of the article ID
For exapmle,  Article Table content is:

1, "Title1","2,6"
2, "Title2",""
3, "Title3","6,1"

6, "Title6","2"
Lets say I want to get all titles of article ID 1. I am going to its FAID which is  "2,6"
So the query will return : "Title2", "Title6"
 
Can you advice how to write this? I can do a walk around solution where I will open a new table name FA but I rather not to.
 

View 3 Replies View Related

Missing Commas

Feb 18, 2008

I have a normal nvarchar(50) field in a table that has a single comma in it but when i read it using OleDbDataReader the comma gets removed. 
any ideas what i am doing wrong ?
 
many thanks

View 4 Replies View Related

Commas In Excel

May 10, 2008

Hi

I have an excel file with 2 columns:
article art_group
kb art,tp,key
portal grup,port,li

I have a table in the database called tb_artGroups with the columns

article art_group

Now for each row in the excel i have to write the values after the comma in a new row such as:
article art_group
kb art
kb tp
kb key
portal grup
etc...

How can i do that with SSIS?

Thanks





Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 4 Replies View Related

Commas In The Result Set

Sep 4, 2007



I am using SQL 2005 and have a query that returns seven columns of data. In one the columns, the results contains commas. For example, one of the results is 'brooke, nick, dustin, and me.jpg'. The results are saved into a .csv file so it can be imported into Excel. The user named the file with the commas and I need to preserve the original file name.

My question:
Is there some way to format the data inside of SQL so that when it gets saved as a .csv file, the extra commas don't throw off the column layout?

I don't want to have to manually clean up possibly several hundred rows of data everytime I run the query.

Thanks in advance.

View 4 Replies View Related

Select Many Records Into 1 Comma Seperated Field.

Oct 30, 2006

I want to pull out some information for documenting my user accounts.I want to be able to show all of the users and what companies those users have access to.

The table which stores the company information lists it in multiple rows, so there will be many results for each user
user -- companyid
johnd -- 2
johnd -- 5
johnd -- 1

I want to select this into one record so it will show up like this
user -- companies
johnd -- 2,5,1

The only way I know how to do this would be with a sub-query that uses a cursor to loop through & dump it into one string value, and then output the string value to the field. This seems extremely inefficient for such a seemingly simple task.

Can somebody help me out here?
thanks

View 3 Replies View Related

Comma Seperated Field To Arrays For Each Record?

May 12, 2008

Hello,

My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...

I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.

In the [Audit] table I can find all the field changes for the record in question using this SQL...

select *
from audit
where rowid = 1309606
order by auditID asc


My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...

audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'


audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'



audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1Â 3'



audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'



audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'


The funny character, , is ASCII 127.


So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.


What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?

Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.


Hope that makes sense and any help would be great!


Cheers,
Nick

View 6 Replies View Related

Select Distinct On Field With Values Seperated By

Apr 5, 2006

I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma.

If I run the following command.

select distinct(DSCODES) from DB

The following is returned.

S100,S102,S103
S100,S103,S105

What I would like returned is the following

S100
S102
S103
S105

Is this possible?

Thank you in advance

Graham

View 2 Replies View Related

Field Contains Multiple Values Seperated By Comma

May 3, 2006

Hi All

I'm really new to SQL and would appreciate a solution to the following problem.

I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.

C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202

I need a solution to select all values from CODES that are distinct so I end up with the following.

C200,C201,C202,C203,C204,C205,C206

Any help would be really appreciated. Please bear in mind I'm a complet novice.

View 4 Replies View Related

How Do I Create An In-line Comma Seperated List

Jul 20, 2005

Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?

View 3 Replies View Related

Extracting Multiple Values As Comma Seperated

Jul 17, 2007

Hi all,



This is my table :

WorkstationNo UserID

101 a1

102 a2

103 a3

101 a2



and there are many other fields too. The output I need is something like this



WorsktationNo OccupiedBy

101 a1,a2

102 a2

103 a3



In the similar fashion I would also require to retrieve the values based on the UserID something like this

UserID Workstations

a1 101

a2 101,102

a3 103



Could someone tell me how to write the query for this.



View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved