How To Split A Comma In A String
I have a string
@string = 'abc,def,ghi'
Now I have to display
abc
def
ghi
I have to separate a comma in that
Is there an function like split in sql
plz help me
Thanks in Advance
Suresh Kumar
View Complete Forum Thread with Replies
Related Forum Messages:
UDF To Split A Comma Separated List
Hello. I need to write a UDF that would split a comma separated list and return 4 values. I need to return the first 4 values and ignore the commas after that. If there are no commas in the string that's passed then just return the table with empty strings. The UDF should accept 2 inputs. The ntext and a position and return a value based on the position.For example: 1,2,3,textshould createPosition | Value-------------------------1|12|23|34|textand return a value based on the position. If there are more than 3 commas for example1,2,3,This string, though short, contains a commashould createPosition | Value-------------------------1|12|23|34|This string, though short, contains a commaand return a value based on the position. And if there are are less than 3 commas in the string passedFor example: 1,2 or NULL or 2:3.5 or This is a string with no commasshould createPosition | Value ------------------------- 1| (empty string) 2| (empty string) 3| (empty string) 4| (empty string)and return a value based on the position.This is what I wrote so far. CREATE function GetValueFromPosition (@Input nvarchar(4000), @position int)Returns nvarchar(4000)AsBegin -- Declare the return Variable Declare @ReturnValue nvarchar(4000) Select @ReturnValue = LTRIM(RTRIM(member_id)) From dbo.SplitString(@Input, ',') Where position = @position Return @ReturnValueEnd CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')-- This function splits a string of CSV values and creates a table variable with the values.-- Returns the table variable that it createsRETURNS @Strings TABLE( position int IDENTITY PRIMARY KEY, member_id varchar(8000))ASBEGIN Declare @index int Set @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURNEND I am trying to modify these according to what I need but its not working. Please help. Thank you.
View Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Please - Split Select From Table To Comma 1,2,3,...
help please i have this table number_row fld_index vtext ---------------------------------------- 1 101 a 2 101 b 3 101 c 4 102 d 5 102 e 6 102 f 7 103 g 8 103 h 9 103 i ...... ...... .... now i need to do this SELECT fld_index FROM table_index GROUP BY fld_index ---------------------------- and i get 101 102 103 104 how split this select and do this in('101' ,'102','103','104') ------------------------------ Code Snippet DECLARE @aaa set @aaa =(SELECT fld_index FROM table_index GROUP BY fld_index) set fld1 = CASE WHEN fld1 in(@aaa ) then '*' else fld1 end ---- need to split the @aaa ,'101' ,'102' , '103','104' , ---------------------------------------------------------------------------------------------------------------------------- instead of this update update [dbo].[tb_pivot_big] set fld1 = CASE WHEN fld1 in('101' ,'102','103','104') then '*' else fld1 end , fld2 = CASE WHEN fld2 in('101' ,'102','103','104') then '*' else fld2 end , fld3 = CASE WHEN fld3 in('101' ,'102','103','104') then '*' else fld3 end , fld4 = CASE WHEN fld4 in('101' ,'102','103','104') then '*' else fld4 end , fld5 = CASE WHEN fld5 in('101' ,'102','103','104') then '*' else fld5 end from [dbo].[tb_pivot_big]
View Replies !
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
We have the following two tables : Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) ) The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members. The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in). We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table. For instance, Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table. Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View Replies !
Help Split List Of Email Add Comma For Evry Email
need help split list of email add comma for evry email i have tabe "tblLogin" and in this table i have field emall like this emall ----------------------------------------- aaa@hhhh.mm nnn@hhhh.mm mmm@hhhh.mm need to do ilke this Code Snippet @list_email = (SELECT emall FROM tblLogin) --------------------------i get this -----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm @recipients = @list_email Code Snippet IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] ) BEGIN DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX) SET @xml =CAST(( SELECT FirstName AS 'td','', LastName AS 'td','' , Date_born AS 'td','' , Age AS 'td','' , BirthdayToday AS 'td','' , BirthdayThisWeek AS 'td' FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl> <tr> <td>name</td> <td>fname</td> <td>date</td> <td>age</td> <td>aaaaaaaaa</td> <td>bbbbbbbbbbbbbbb</td> </tr>' SET @body = @body + @xml +'</table></body></html>' EXEC msdb.dbo.sp_send_dbmail @recipients =N'rrr@iec.co.il', @copy_recipients='rrrrr@iec.co.il', @body = @body, @body_format ='HTML', @subject ='ggggggggggggggggggggg', @profile_name ='ilan' END ELSE print 'no email today'
View Replies !
Comma Separated String To Int
I have a checkbox list on datalist as one column. when user selects more than one checkbox and click on apply. i concatenate IDs of checkboxes as '1,2'3' for e.g. and sending that to Stroe Procedure as varchar datatype parametrer. In Procedure i wanna update status of all three selected and i am using statement "update tbl set status=1 where pageid in('1,2,3'). It is saying it cannot convert varchar to int. How can i do this task? Thanks in advance.
View Replies !
Remove Last Comma From The String
Hi All, I have one field with different values like F1 A,B,C, D,E G,H,I, My requirement is to remove the last comma from that string. I need the output should look like F1 A,B,C D,E G,H,I thanks, Mears
View Replies !
Quotes Around The Comma Delimited String
I am trying to build a dynamic where statement for my sql stored prcoedure if len(@Office) > 0 select @strWhereClause = N'cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText( ' + @vchOffice + ',' + ''',''' + '))' + ' and ' + @strWhereClause In this case users can enter comma delimited string in their search criteria. So if they enter we1, we2, we3 then my sql statement should look like select @strWhereClause = cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText('we1', 'we2', 'we3'),',') My csfParseDeLimitedText function looks like this Create FUNCTION [dbo].[csfParseDeLimitedText] (@p_text varchar(4000), @p_Delimeter char(1))RETURNS @results TABLE (id varchar(100))ASBEGIN declare @i1 varchar(200)declare @i2 varchar(200)declare @tempResults Table (id varchar(100))while len(@p_text) > 0 and charindex(@p_Delimeter, @p_text) <> 0beginselect @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)insert @tempResults select @i1select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))endinsert @tempResults select @p_text insert @resultsselect *from @tempResultsreturnEND My problem is it does not put quotes around the comma delimited stringso I want to put 'we1' , 'we2'. These single quotes are not coming in the dynamic sql statement. How can I modify my query so that single quotes around each entry should show up. Any help will be greatky appreciated. Thanks
View Replies !
Is This An Efficient Way To Create A Comma String
Hi there,I have created a sp and function that returns amongst other things acomma seperated string of values via a one to many relationship, thecode works perfectly but i am not sure how to test its performance.. Isthis an efficient way to achieve my solution.. If not any suggestionshow i can improve it.. What are the best ways to check query speed???MY SP:CREATE PROCEDURE sp_Jobs_GetJobsASBEGINSELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)AS location, salary, summary, logoFROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.idORDER BY j.Inserted DESCENDGO--------------------------------------------MY Function:CREATE FUNCTION fn_GetJobLocations (@JobID int)RETURNS varchar(5000) ASBEGINDECLARE @LocList varchar(5000)SELECT @LocList = COALESCE(@LocList + ', ','') + ll.location_nameFROM Jobs_Locations l inner join List_Locations ll onll.LocationID = l.LocationIDWHERE l.JobID = @JobIDRETURN @LocListENDAny help or guidance much appreciated...
View Replies !
Sql Statement Comma Separated String
I have a table called evidence, which has the following Fields | evidence_id | Description| Standards| E001 blagh 1.1,1.2,1.3 Ok I am trying to search the comma-separated string in the standards field using the like clause so I can display the evidence_id. SQL looks like SELECT Evidence.Standards, * FROM Evidence WHERE (((Evidence.Standards) Like '%1.1%')); However it will not search through the list and select for example if I change 1.1 to 1.2. The commas wont allow it. It works if I just have one item in the list that is just 1.1. Can anyone help me to search a comma-separated string for a certain string? Thanks Asylum
View Replies !
Splitting A Comma Delimited String
I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be 3:4:5:4 2:4:1 4:1:2:5:2 I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows: Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255) Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2' While @loc != 0 begin set @prevloc=(case when @loc = 1 then 0 else @loc end) +1 set @loc = charindex(',',@person,@loc+1) Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end)) print 'String = ' + @Str set @cnt=@cnt+1 end RESULT ------ String = 3:4:5:4 String = 2:4:1 String = 4:1:2:5:2 Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.
View Replies !
SELECT WHERE IN Comma Delimited String
Hello I have a table with column Options where each field contains a comma delimited list of ID numbers. I want to select any records where a certain ID number appears in that list. So something like: SELECT * FROM Table t1 WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID) But that gives me the error: Syntax error converting the varchar value '39,20' to a column of data type int. I feel I'm close though! Could anyone point me in the right direction? Many thanks Square
View Replies !
Multiple Rows Into A Comma Delimited String
I have the following table:id name1 yes2 no3 what4 is5 this6 niceThe amount of rows can vary from 1 to 50. I only need the name column.What SQL statement do I have to execute to get the following:yes,no,what,is,this,nice, (trailing , is acceptable)Thanks!
View Replies !
How To Parse A String Column With Comma Delimited
Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156
View Replies !
Inserting Into ONE Column A Comma Delimted String??
I have a string of comma delimited values such as the following: 1,2,3,4,5 I have a table with ONE column only. How can I do an insert statement which will insert each of these values? If I type the following: "INSERT INTO tabletest SELECT 1,2,3,4,5" I get this error: "Insert error: column name or number of supplied values does not match table definition. " This makes sense because I do not have 5 columns I only have 1. But how can I get around this? Any help is most appreciated! Thanks in advance...mary
View Replies !
How To Split A String Using Sql
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend. Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns. Thanks
View Replies !
Split A String
Hi I have this string which might have a hyphen in it "-" What I want to do is if I get a hyphen then take all characters after hyphen else take only all the characters starting from the 5th position of the string How can this be achieved?
View Replies !
Split A String In Sql
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it select a name for each value and return a recordset. Any pointers greatfully received. First attempt is dreadfully slow as I am opening recordsets each time Function func_getFood() Dim rsfoodsql Dim foodoutput for x=1 to ubound(masterfoodarray)-2 set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'") if not rsfoodsql.eof then foodoutput=rsfoodsql("foodname") if not foodoutput="" then response.write foodoutput&"<BR>" end if end if next End Function Hope someone can help, cheers
View Replies !
How To Split The String
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query any one know help me Regards, Prabu R
View Replies !
Help Required For Splitting Up String Variable Using Comma Separator
I need a help in SQL Server 2000. I am having a string variable in the format like -- (1,23,445,5,12) I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop. Can anyone help me out in splitting the variable using the comma separator...
View Replies !
How To Split A Delimeted String...
hi, i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines", the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less) now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter. e.g @item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10" if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null. any response will be appreciated. thanks.
View Replies !
SQL String Split With | Pipe
I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont. My code below: Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''") Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection) Dim dt As New DataTable() adapter.Fill(dt) elbValue.DataSource = dt elbValue.DataBind()
View Replies !
Sql Charindex Split String
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
View Replies !
Split The String Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
View Replies !
Split String Using Delimiter
Hi, I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2' I need to split the above string and get each of those values... ' / ' delimiter Can some one please help on this. Thanks
View Replies !
Split Command In String
hi all, i am still a newbie in sql i got a problem with my query for example: ------------------------------- DECLARE @LongString VARCHAR(10) @LongString = "Hello-Word" ------------------------ i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql Thanks a lot guys arifliminto86
View Replies !
Conditional Where Clause With Comma Delimited String And Link Table
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
View Replies !
Passing A Comma Delimited String Of Parameters To A Stored Proc
Hello, I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets. I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc. But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections? Thanks.
View Replies !
Passing Multiple String Values Separted By A Comma As One Parameter
Hello, I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before. Thanks a lot for any feedback!
View Replies !
SPLIT A String Inside SSIS/T-SQL Code?
I have a RPC which gives me multiple - single record rows like Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column Now I need to split the above record into 6 Fields and populate in my local DB I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's Any ideas / directions?
View Replies !
Comma Delimited File With Comma Inside A Column
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN, it was never a problem in DTS 2000. any work around. Thanks,
View Replies !
Concatenated String Of Comma Separated Values (was &"Help With Query&")
I have following 2 queries which return different results. declare @accountIdListTemp varchar(max) SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id) FROM (SELECT Distinct acct_id FROM SomeTable) Result print @accountIdListTemp The above query return the values without concatenating it. declare @pot_commaSeperatedList varchar(max) SELECT DISTINCT acct_id into #accountIdListTemp FROM SomeTable SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id) FROM #accountIdListTemp print @pot_commaSeperatedList drop table #accountIdListTemp This query returns result as concatenated string of comma separated values. If i want to get similar result in a single query how can i get it?
View Replies !
Using A Comma-separated String Using Stored Procedure And &"IN&"
Hello, I was wondering if it's possible to pass in a comma separated string "12,14,16,18" and use it in a stored procedure with "IN" like this: @SubRegions varchar(255) <-- my comma separated string SELECT * FROM myTable WHERE tbl_myTable.SubRegionID IN (@SubRegions) It tells me it has trouble converting "'12,14,16,18'" to an INT. :(
View Replies !
Comma In Value Within Comma Separated File
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes. But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half. Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...) I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified. Any ideas? Here is sample fie content to see what I mean: 342123, Jason, 12345 21, Kim,4567 32.43, John Paul, 1245 23, "Mr. T", 98764 12, "Peter, Paul, Mary", 09643 The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last. (Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
View Replies !
SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist. Here is an approach I am trying: TABLE Events(EventID int, Categories varchar(200)) EventID Catetories -------------------------- 1 ‘6,8,9’ 2 ‘2,3’ PROCEDURE ListFilteredEvents @FilterList varchar(200) -- contains ‘3,5’ AS SELECT EventID FROM Events WHERE (any value in Categories) IN @FilterList Result: EventID ---------- 2 How can I select all records where any value in the Categories column matches a value in @FilterList. In this example, record 2 would be selected since it belongs to category 3, which is also in @FilterList. I’ve looked at the table of numbers approach, which works when selecting records where a column value is in the parameter list, but I can’t see how to make this work when the column itself also contains a comma delimited list. Can someone suggest an approach? Any examples would be greatly appreciated! Gary
View Replies !
Comma Separated Value
Suppose I have a table like this code Value 1 a 1 a 1 b 2 c 2 c 1 d 2 g Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d My output should be like this 1 a,b,d 2 c,g Can anybody help me I can I do this with the help of a cursor or any other way? Subhasish
View Replies !
Comma Delimited Row
Hi, Good evening! I have a problem. I have a namelist coming from a distribution list of an active directory. When I converted it to csv file, the members reside in just one column and separated by a comma. I want the names to be separated in a row one by one. I tried it on excel and I used the transpose column but to no avail. My last resort is to import it on sql but the names on the column was cut and not complete. Do you have any idea how to do this. Your help is highly appreciated. this is the sample file.. names kelly.yap, lizzy.fox, yahoo, finance.dep, hope.miller, porly.john the maximum names in a row is 566. thanks in advance. myBU
View Replies !
Comma Separated Value
I’m passing a comma separated parameter (home, world, child) into stored procedure. I have a Slitter function which is basically creates a table out of delimited list. My stored procedure needs to find matched records in one of the table based on delimited list. I have something like this: SELECT * FROM Word WHERE WordName IN (SELECT * FROM dbo.fxSplitter('home,world,child, ',') I would like to have my stored procedure be able to select rows, even if comma delimited parameter holds part of the name like this “hom, wor, chil� . Another words it will be SELECT * FROM Word WHERE WordName LIKE '%hom%' OR WordName LIKE '%wor%' OR WordName LIKE '%chil%'
View Replies !
SQL Comma Seperated List
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 Replies !
Comma Deliminited Coloumn
I have two tables. One table (Table 1) has a column containing commadelaminated email addresses. The other table (Table 2) has a columnwith just one email address in it. I need to perform a query thatjoins the comma delaminated table (Table 1) to Table 2, when the singleemail address in Table 2, is contained in the list of email addressesin (Table 1).I hope this isn't too cryptic, and I know comma delaminated lists arebad, but I can't do anything about that.I need a select statement that can perform this task. Anyone have anysuggestions?
View Replies !
Comma Delimited Results Help Please
Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated. tblservers servid servername 1 server1 2 server2 3 server3 tblapplications appid appname 1 app1 2 app2 3 app3 tblapplink id appid servid 1 1 1 2 1 2 3 1 3 4 2 1 5 2 3 6 3 1 we want to display this information as below: appname serverlist app1 server1, server2, server3 app2 server1, server3 app3 server1 Thank you very much, mrtwo
View Replies !
Get Values Separated By Comma
Hello, I need your advice. Here's my scenario. Table A ------------ id name 100 apple 115 grape 125 tomato 145 melon Table B ------------- id Fruits 11 100, 115, 145 12 125, 115 13 100 I thought i could get the list of fruits using this statement: select name from A where id IN (select fruits from B where id = 11) But apparently not, it's working if select name from A where id IN (select fruits from B where id = 13) That means it does not recognize values seperated by comma. Anyone who has any idea how to make it work? Thanks in advance. HS.
View Replies !
How To Extract Data After The Comma
hi there i have a field name(fil_srt_cond) with the values of cmpnt_name,ASC,1,2 cmpnt_stuff,DESC,2,3 i used the char index:- substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord, substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len, substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str to display them in their specific fields like NAME STAR_CHAR LENGTH ORDER Tag 1 2 ASC however for cmpnt_stuff,DESC,2,3 i'm getting NAME STAR_CHAR LENGTH ORDER Stuff , , DES therefore i'm not getting the required values 2 and 3. can u pls help me to find a way how to get the data after the comma. thank you in advance
View Replies !
|
TRACKER
