6

I'm using the split function from this SQL Team thread (second post) and the following queries.

--This query converts the interests field from text to varchar select cp.id ,cast(cp.interests as varchar(100)) as interests into #client_profile_temp from client_profile cp --This query is supposed to split the csv ("Golf","food") into multiple rows select cpt.id ,split.data from #client_profile_temp cpt cross apply dbo.split( cpt.interests, ',') as split <--Error is on this line

However I'm getting an

Incorrect syntax near '.' error where I've marked above.

In the end, I want

ID INTERESTS 000CT00002UA "Golf","food"

to be

ID INTERESTS 000CT00002UA "Golf" 000CT00002UA "food"

I'm using SQL Server 2008 and basing my answer on this StackOverflow question.

Improve this question

4 Answers 4

Reset to default
10

TABLE

x-----------------x--------------------x | ID | INTERESTS | x-----------------x--------------------x | 000CT00002UA | Golf,food | | 000CT12303CB | Cricket,Bat | x------x----------x--------------------x


METHOD 1 : Using XML format

SELECT ID,Split.a.value('.', 'VARCHAR(100)') 'INTERESTS' FROM ( -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one SELECT ID, CAST ('<M>' + REPLACE(INTERESTS, ',', '</M><M>') + '</M>' AS XML) AS Data FROM TEMP ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)

METHOD 2 : Using function dbo.Split

SELECT a.ID, b.items FROM #TEMP a CROSS APPLY dbo.Split(a.INTERESTS, ',') b

And dbo.Split function is here.

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end

FINAL RESULT

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

In the second method 'split' is not an inbuilt function? I see reference to split in many articles thought it was inbuilt in SQL. Thought it could be inbuilt.
No. It is not. But many people uses that since they are unaware of the inbuilt XML function. @Nishant
Its a good idea to convert regular data to a XML and work on it.
Yes it is. But one should be careful when the CSV has tags in it, ie <, > etc because XML will treat < as &gt and > as &lt but we can use REPLACE function to replace them. @Nishant
I'm digging that XML solution. I always thought it was annoying how there was no native Split() function.
|
8 
from #client_profile_temp cpt cross apply dbo.split( #client_profile_temp.interests, ',') as split <--Error is on this line

I think the explicit naming of #client_profile_temp after you gave it an alias is a problem, try making that last line:

 cpt.interests, ',') as split <--Error is on this line

EDIT You say

I made this change and it didn't change anything

Try pasting the code below (into a new SSMS window)

create table #client_profile_temp (id int, interests varchar(500)) insert into #client_profile_temp values (5, 'Vodka,Potassium,Trigo'), (6, 'Mazda,Boeing,Alcoa') select cpt.id ,split.data from #client_profile_temp cpt cross apply dbo.split(cpt.interests, ',') as split

See if it works as you expect; I'm using sql server 2008 and that works for me to get the kind of results I think you want.

Any chance when you say "I made the change", you just changed a stored procedure but haven't run it, or changed a script that creates a stored procedure, and haven't run that, something along those lines? As I say, it seems to work for me.

Improve this answer

5 Comments

I made this change but it didn't change anything.
@CavynVonDeylen I edited my response, showing a session I tried it in, where works as I think you want. I'm thinking if you saved the select as a view, you have to alter the view. Or are you just typing in literally what you're showing us?
I pasted your code into a new query, but I got an "Incorrect syntax near ',' on the 'Vodka,Potassium,Trigo' line, and the same error as before on the cpt.interests line. I'm starting to think the issue is a little larger than my syntax, since I was having some issues doing pivots before as well. Thanks for your input though.
@CavynVonDeylen, I suspect some fancy pasting is doing something weird to the single quote character? If you just type at the keyboard: "select 'abc'", (without double quotes, but with single quotes) vs. if you try to paste it in, any difference?
I was talking with a coworker and it turns out our database is actually running Server 2005, while I'm using SSMS 2008. (I didn't realize there could be that discrepancy) This seems to be the reason for my errors. Thanks for your help.
3

As this is old, it seems the following works in SQL Azure (as of 3/2022) The big changes being split.value instead of .data or .items as shown above; no as after the function, and lastly string_split is the method.

select Id, split.value from #reportTmp03 rpt cross apply string_split(SelectedProductIds, ',') split
Improve this answer

Comments

0

Try this:

--This query is supposed to split the csv ("Golf","food") into multiple rows select cpt.id ,split.data from #client_profile_temp cpt cross apply dbo.split(cpt.interests, ',') as split <--Error is on this line

You must use table alias instead of table name as soon as you define it.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.