Hello Experts I have a table like this ID CommaSeperatedString 1 a,b,c,d,e 2 x,y,z What I want is to convert this into ID Text 1 a 1 b 1 c 2 x 2 y like this. Is it possible?
There are quite a few split functions out there. Basicall they all do something similar to SELECT T.id, RIGHT(LEFT(T.csv,Number-1), CHARINDEX(',',REVERSE(LEFT(','+T.csv,Number-1)))) FROM master..spt_values, your_table T WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(T.csv)+1 AND (SUBSTRING(T.csv,Number,1) = ',' OR SUBSTRING(T.csv,Number,1) = '')
Hello Frank Kalis Say I have a UDF function called SplitCSVToTable which returns a table of splited items. I have another table TableA --> ID CSVString 1 a,b,c,d 2 x,y,z How can I use it in a select statement like Select ID ,dbo.SplitCSVToTable(CSVString) From TableA
You would put the UDF in the FROM clause, with an alias, like this: SELECT f.* FROM dbo.SplitCSVToTable(CSVString) f ... and treat it like you would treat any (read-only) table.
The way you describe it, you're looking now for a scalar function, but that won't work when you want to return the split strings as a table. What should your result set look like?
See thsi http://www.logiclabz.com/sql-server...break-comma-separated-strings-into-table.aspx for more information.
Thank you Satya,Adriaan and Frankkalis for the information. Sorry for not following up on this, was having a short vacation. The format I am looking for is same as my first post. SplitCSV function return a table and it works fine when you process one item. But when you want to pass the value for the input parameter of the function and try to treat the return value of the SPLITCSV as a subtable it breaks. ID CommaSeperatedString 1 a,b,c,d,e 2 x,y,z ID and CommaSeperatedString will be my paramter to the splitCSV function and that will return a table with rows as ID Text 1 a 1 b 1 c 1 d 1 e and I should be able to link the ID of main table and result from UDF using the ID. I tried the method Aadrian has mentioned but not able to pass the CSVString as a column of a table.
