March 2, 2012 at 4:43 am
How to in a SELECT statement transform a csv of int-values in varchars?
I have 2 tables:
One with Names and a Csv list of integers. Example:
'John' '1,2'
And a lookup table. Example:
1 'cola'
2 'beer'
What I want as result is:
'John' 'cola beer'
This works fine:
SELECT (SELECT ' '+Csv AS 'data()' FROM Csvs WHERE CsvID IN (1,2) FOR XML PATH('') ) AS DrinkOrder
--cola beer
But when I try this with "a field containing (1,2)" then I get a conversion error.
Is this not possible? Or do I need to alter my SELECT Statement? And what would be the correct statement?
Some test-data:
USE TEMPDB
CREATE TABLE dbo.CSVs (CsvID INT, Csv VARCHAR(50));
INSERT INTO CSVs VALUES (1, 'cola')
INSERT INTO CSVs VALUES (2, 'beer')
INSERT INTO CSVs VALUES (3, 'wine')
--SELECT * FROM Csvs
CREATE TABLE dbo.Drinks (Person VARCHAR(50), Csv VARCHAR(50));
INSERT INTO Drinks VALUES ('John', '1,2' )
INSERT INTO Drinks VALUES ('Pete', '1,2,3')
INSERT INTO Drinks VALUES ('Mary', '3' )
INSERT INTO Drinks VALUES ('Andy', '' )
--SELECT * FROM Drinks
SELECT (SELECT ' '+Csv AS 'data()' FROM Csvs WHERE CsvID IN (1,2) FOR XML PATH('') ) AS DrinkOrder
--cola beer
--SELECT Person, (SELECT ' '+Csv AS 'data()' FROM Csvs WHERE CsvID IN (Csv) FOR XML PATH('') ) AS DrinkOrder FROM Drinks
--Conversion failed when converting the varchar value 'cola' to data type int
--Wanted result:
--Person DrinkOrder
-- John cola beer
-- Pete cola beer wine
-- Mary wine
-- Andy
March 2, 2012 at 6:27 am
Use string splitter function (SQL one based on Tally-table is well described in http://www.sqlservercentral.com/articles/Tally+Table/72993/ by Jefff Moden).
March 2, 2012 at 6:53 am
Sorry Eugine,
I looked at the article you mentioned. An article from Jeff Moden is always a good reference. But this article is beyond my skills. 🙁
I have some control over the input.
Could it help when I use '1','2' in the Persons.csv column in stead of the stored 1,2 ?
March 2, 2012 at 7:40 am
Henk Schreij (3/2/2012)
Sorry Eugine,I looked at the article you mentioned. An article from Jeff Moden is always a good reference. But this article is beyond my skills. 🙁
I have some control over the input.
Could it help when I use '1','2' in the Persons.csv column in stead of the stored 1,2 ?
There a number of poor performing RBAR versions of this in his article. You can find a ton of other references around the internet doing the same basic looping structure to accomplish this. At the end of the day you have to split your string and by far the best approach to this is in Jeff's article.
It would be easier if you used 1,2 instead of '1','2' but either way you need to parse that string into multiple fields.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2012 at 7:42 am
I think the key to this all is to transform the value '1,2' into
SELECT 1 UNION ALL SELECT 2
(or SELECT '1' UNION ALL SELECT '2', as then automatic conversion will do its job)
Something like to unwrap a csv into its funding parts.
There should be a smart solution to this, but which?)
March 2, 2012 at 7:46 am
Nope, you will not.
What you need to do is to have the splitter function.
It may be that I will be killed by the community for the following advice :hehe::
If your table is small (few thousands records max) and you don't care much about great performance, you can use any splitter function. As simple as:
CREATE FUNCTION [dbo].[f_SplitString]
(
@TextVARCHAR(MAX),
@DelimiterVARCHAR(5)
)
RETURNS @Result TABLE (OccurenceId SMALLINT IDENTITY(1, 1) PRIMARY KEY, SplitValue VARCHAR(8000))
AS
BEGIN
DECLARE @NextPos INT, @LastPos INT, @TextLen INT
SELECT @TextLen = DATALENGTH(@Text)
SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1), @LastPos = 0
WHILE @NextPos > 0
BEGIN
INSERT @Result (SplitValue)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END
IF @NextPos <= @LastPos INSERT @Result (SplitValue) SELECT SUBSTRING(@Text, @LastPos + 1, @TextLen - @LastPos)
RETURN
END
GO
Actully, just seen you are creating permanent tables in temdb. You should never do it!
Create tables with # sign at front of the name (#table), and it will be created there.
Or you can use table variables.
Here is the code:
-- that is how you should setup your case:
CREATE TABLE #CSVs (CsvID INT, Csv VARCHAR(50));
INSERT #CSVs VALUES (1, 'cola')
INSERT #CSVs VALUES (2, 'beer')
INSERT #CSVs VALUES (3, 'wine')
--SELECT * FROM Csvs
CREATE TABLE #Drinks (Person VARCHAR(50), Csv VARCHAR(50));
INSERT #Drinks VALUES ('John', '1,2' )
INSERT #Drinks VALUES ('Pete', '1,2,3')
INSERT #Drinks VALUES ('Mary', '3' )
INSERT #Drinks VALUES ('Andy', '' )
--SELECT * FROM Drinks
-- the final query to get your results out --
-- the folloiwng CTE will get use of the split function
-- and will lookup a drink name by id
;with cte_PD
AS
(
select d.Person
,c.Csv as Drink
from #Drinks as d
OUTER APPLY dbo.f_SplitString(d.Csv,',') as s
LEFT JOIN #CSVs c ON c.CsvID = CAST(s.SplitValue AS INT)
)
-- we use for xml path to concatenate drink names
select t1.Person
,( select t2.Drink + ' '
from cte_PD t2
where t2.Person= t1.Person
order by t2.Drink
for xml path('') )
from cte_PD t1
group by t1.Person
March 2, 2012 at 7:58 am
Oops,
That's a massive answer 🙂
I'll study your answer. Thanks a lot.
My lookup table is just 20 records, and will never have nore then 50 records.
My drinks table is at most 10.000 records. So a simple solution will do the trick.
btw, which language is this:
O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh
It sounds like an very old saying (except for the microsofta) 😉
March 4, 2012 at 7:00 pm
Eugene Elutin (3/2/2012)
It may be that I will be killed by the community for the following advice :
Ummmm... so what's your address, Eugene? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2012 at 7:12 pm
Henk Schreij (3/2/2012)
Oops,That's a massive answer 🙂
I'll study your answer. Thanks a lot.
My lookup table is just 20 records, and will never have nore then 50 records.
My drinks table is at most 10.000 records. So a simple solution will do the trick.
btw, which language is this:
O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh
It sounds like an very old saying (except for the microsofta) 😉
I have an old saying, as well. "If you want it real bad, that's the way you'll normally get it". 😉
Don't use the While loop splitter no matter how simple you think your requirements are. Go get the correct splitter from the article that folks have recommended.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2012 at 3:01 am
Jeff Moden (3/4/2012)
Eugene Elutin (3/2/2012)
It may be that I will be killed by the community for the following advice :Ummmm... so what's your address, Eugene? 😉
Jeff, personally, you have no moral ground to kill me, as I've advised OP to read about and use your splitter, check the reason why he didn't go the advised direction. :hehe:
Usually, I'm using CLR implementaion of spliiter or yours one. Howevere, I beleive everyone here have seen (and not once) loop-based splitters in many database implentations. I think it's due to the simple fact: you can not clone yourself into as many instances as needed to get every SQL database to have it done right 😀
March 5, 2012 at 4:41 am
What I learned from this discussion is: there is no easy 'set based' solution to the problem of unwrapping a csv with sql.
(a solution, like the other way around: it is easy to transform a set of values in a csv string with FOR XML PATH('') )
The solution with a tally table and a function is to complicated for me.
So I wrote just a few lines of code in Pascal to solve this problem in an easy way.
Thank you for helping me, even without using your solutions. 🙂
March 5, 2012 at 2:10 pm
Henk Schreij (3/5/2012)
What I learned from this discussion is: there is no easy 'set based' solution to the problem of unwrapping a csv with sql.(a solution, like the other way around: it is easy to transform a set of values in a csv string with FOR XML PATH('') )
The solution with a tally table and a function is to complicated for me.
So I wrote just a few lines of code in Pascal to solve this problem in an easy way.
Thank you for helping me, even without using your solutions. 🙂
Here is a simple solution to return a comma-separated multi-valued string as a resultset. Using the Replace function, you can transform the commas as XML tags and cast the column as XML. Once done, you're just working with XML.
declare @s-2 varchar(8000), @x xml;
-- Input string contain IDs as comma separated list:
select @s-2 = 'a,b,c,1,2,3';
-- Transform CSV string to XML string and stuff in XML type variable.
-- We'll get an error here, if the XML string is not validly formed:
select @x = '<id>'+replace(@s,',','</id><id>')+'</id>';
-- Select set of IDs from XML variable using the nodes method:
select items.id.value('.','varchar') as id from @x.nodes('id') as items(id);
id
----
a
b
c
1
2
3
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 2:34 pm
Eric M Russell (3/5/2012)
Henk Schreij (3/5/2012)
What I learned from this discussion is: there is no easy 'set based' solution to the problem of unwrapping a csv with sql.(a solution, like the other way around: it is easy to transform a set of values in a csv string with FOR XML PATH('') )
The solution with a tally table and a function is to complicated for me.
So I wrote just a few lines of code in Pascal to solve this problem in an easy way.
Thank you for helping me, even without using your solutions. 🙂
Here is a simple solution to return a comma-separated multi-valued string as a resultset. Using the Replace function, you can transform the commas as XML tags and cast the column as XML. Once done, you're just working with XML.
declare @s-2 varchar(8000), @x xml;-- Input string contain IDs as comma separated list:
select @s-2 = 'a,b,c,1,2,3';
-- Transform CSV string to XML string and stuff in XML type variable.
-- We'll get an error here, if the XML string is not validly formed:
select @x = '<id>'+replace(@s,',','</id><id>')+'</id>';
-- Select set of IDs from XML variable using the nodes method:
select items.id.value('.','varchar') as id from @x.nodes('id') as items(id);
id----
a
b
c
1
2
3
Yes, you certainly could do that. The splliter article (link previously supplied) explains why you might not want to use that method. It's really not much better than a While Loop for performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2014 at 8:41 am
I like charindex() for this.
convert the int field to character and wrap in delimiters and then replace the commas in CSV with the delimiters and wrap in delimiters too.
SELECT '|' + LTRIM(STR(intField)) + '|', '|'+REPLACE(charFieldWithCSV,', ','|')+'|'
FROM TableWithIntField i
INNER JOIN TableWithCSVcharField ON CHARINDEX( '|' + LTRIM(STR(intField)) + '|', '|'+REPLACE(charFieldWithCSV,', ','|')+'|') >0;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply