October 7, 2013 at 9:31 am
Hello again,
I am running into a problem importing a string containing an array into a table.
My string looks like '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
I want to import this into a temp table that looks like:
ID, Fname, Lname, Sex
1, mark, peters, mr
2, jane, fonda, mrs
3, john,doo,mr
4, james,bond,mr
Can someone help me with this? I really have no clue how to solve this.
Thanks a lot!
Mike
October 7, 2013 at 9:43 am
There are any number of solutions to this issue. Search for "comma delimited strings" in SQLServerCentral and you will get plenty of examples of functions that will do just as you need.
October 7, 2013 at 1:27 pm
Thanks jmcgarvey,
The problem for me is not the comma delimetered insertion. For me the problem is to add multiple records with multiple columns into a table.
Hope that you, or someone else, can help me with this...
Thanks again
Mike
October 7, 2013 at 1:55 pm
Here's what I came up with using the DelimitedSplit function refernced in my signature:
declare @val varchar(8000)
set @val= '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]';
WITH cte AS
-- separate the input values into "rows" and eliminate the leading and trailing bracket.
(
SELECT
REPLACE(Item,'{','') as row,
ItemNumber as pos,
ROW_NUMBER() OVER (ORDER BY ItemNumber DESC) AS r
FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(@val,'[',''),']',''),'}')
), cte_splittedValues AS
-- separate the input values into "columns" excluding the last "row" that would only be an empty string
(
SELECT x.ItemNumber, x.Item, cte.pos, cte.r
FROM cte
CROSS APPLY
(
SELECT *
FROM dbo.DelimitedSplit8K(cte.row,',')
)x
WHERE cte.r >1
)
-- use the "old-fashioned" Cross-Tab approach to get the table backt together
SELECT
Pos AS Id,
MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE NULL END) AS Fname,
MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE NULL END) AS Lname,
MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE NULL END) AS Sex
FROM cte_splittedValues
Group by Pos
ORDER BY Pos
October 7, 2013 at 4:24 pm
You can also use PatternSplitCM (note the link in my signature)....
declare @val varchar(8000)= '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]';
WITH x AS
(
SELECT ItemNumber, Item
FROM dbo.PatternSplitCM(REPLACE(REPLACE(@val,'[{',''),'}]',''),'%[}{]%')
WHERE Matched=0
)
SELECTROW_NUMBER() OVER (ORDER BY x.ItemNumber) AS Id,
MAX(CASE WHEN ps.ItemNumber=1 THEN ps.Item END) AS Fname,
MAX(CASE WHEN ps.ItemNumber=3 THEN ps.Item END) AS Lname,
MAX(CASE WHEN ps.ItemNumber=5 THEN ps.Item END) AS Sex
FROM x
CROSS APPLY dbo.PatternSplitCM(x.item,',') ps
Group by x.ItemNumber
ORDER BY x.ItemNumber;
-- Itzik Ben-Gan 2001
October 7, 2013 at 6:05 pm
Alan.B (10/7/2013)
You can also use PatternSplitCM (note the link in my signature)....
declare @val varchar(8000)= '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]';WITH x AS
(
SELECT ItemNumber, Item
FROM dbo.PatternSplitCM(REPLACE(REPLACE(@val,'[{',''),'}]',''),'%[}{]%')
WHERE Matched=0
)
SELECTROW_NUMBER() OVER (ORDER BY x.ItemNumber) AS Id,
MAX(CASE WHEN ps.ItemNumber=1 THEN ps.Item END) AS Fname,
MAX(CASE WHEN ps.ItemNumber=3 THEN ps.Item END) AS Lname,
MAX(CASE WHEN ps.ItemNumber=5 THEN ps.Item END) AS Sex
FROM x
CROSS APPLY dbo.PatternSplitCM(x.item,',') ps
Group by x.ItemNumber
ORDER BY x.ItemNumber;
Exactly what I was thinking when I saw the problem statement!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 7, 2013 at 6:13 pm
But it can also be done with a single call to PatternSplitCM:
WITH SampleData(MyString) AS
(
SELECT' [{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
)
SELECT MyString
,Col1=MAX(CASE WHEN ItemNumber%6 = 2 THEN Item END)
,Col2=MAX(CASE WHEN ItemNumber%6 = 4 THEN Item END)
,Col3=MAX(CASE WHEN ItemNumber%6 = 0 THEN Item END)
FROM SampleData a
CROSS APPLY dbo.PatternSplitCM(MyString, '[a-zA-Z]') b
WHERE Matched=1
GROUP BY MyString, (ItemNumber-1)/6;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2013 at 12:43 am
Hello all,
Thanks for your help. The solutions you've provided are super and far beyond my current skill level.
Learned a lot, Thanks again!!!
Mike
October 8, 2013 at 12:49 am
Mike Saunders NL (10/8/2013)
Hello all,Thanks for your help. The solutions you've provided are super and far beyond my current skill level.
Learned a lot, Thanks again!!!
Mike
Not sure who said this but it applies in your case:
"Everything is difficult ... until it is easy."
-- Anonymous??
Glad to help out.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 9, 2013 at 12:26 pm
dwain.c (10/7/2013)
But it can also be done with a single call to PatternSplitCM:
WITH SampleData(MyString) AS
(
SELECT' [{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
)
SELECT MyString
,Col1=MAX(CASE WHEN ItemNumber%6 = 2 THEN Item END)
,Col2=MAX(CASE WHEN ItemNumber%6 = 4 THEN Item END)
,Col3=MAX(CASE WHEN ItemNumber%6 = 0 THEN Item END)
FROM SampleData a
CROSS APPLY dbo.PatternSplitCM(MyString, '[a-zA-Z]') b
WHERE Matched=1
GROUP BY MyString, (ItemNumber-1)/6;
Is there anything PatternSplitCM CAN'T do? :Wow:
October 9, 2013 at 1:25 pm
erikd (10/9/2013)
dwain.c (10/7/2013)
But it can also be done with a single call to PatternSplitCM:
WITH SampleData(MyString) AS
(
SELECT' [{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
)
SELECT MyString
,Col1=MAX(CASE WHEN ItemNumber%6 = 2 THEN Item END)
,Col2=MAX(CASE WHEN ItemNumber%6 = 4 THEN Item END)
,Col3=MAX(CASE WHEN ItemNumber%6 = 0 THEN Item END)
FROM SampleData a
CROSS APPLY dbo.PatternSplitCM(MyString, '[a-zA-Z]') b
WHERE Matched=1
GROUP BY MyString, (ItemNumber-1)/6;
Is there anything PatternSplitCM CAN'T do? :Wow:
It can't cook waffles or launch pork chops very well. 😛
_______________________________________________________________
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/
October 9, 2013 at 2:31 pm
Sean Lange (10/9/2013)
erikd (10/9/2013)
dwain.c (10/7/2013)
But it can also be done with a single call to PatternSplitCM:
WITH SampleData(MyString) AS
(
SELECT' [{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
)
SELECT MyString
,Col1=MAX(CASE WHEN ItemNumber%6 = 2 THEN Item END)
,Col2=MAX(CASE WHEN ItemNumber%6 = 4 THEN Item END)
,Col3=MAX(CASE WHEN ItemNumber%6 = 0 THEN Item END)
FROM SampleData a
CROSS APPLY dbo.PatternSplitCM(MyString, '[a-zA-Z]') b
WHERE Matched=1
GROUP BY MyString, (ItemNumber-1)/6;
Is there anything PatternSplitCM CAN'T do? :Wow:
It can't cook waffles or launch pork chops very well. 😛
If you'd like to post your create table statements and some sample data along with some butter and maple syrup I'd be happy to help you with that. :hehe:
October 9, 2013 at 5:37 pm
Mike Saunders NL (10/7/2013)
Hello again,I am running into a problem importing a string containing an array into a table.
My string looks like '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
I want to import this into a temp table that looks like:
ID, Fname, Lname, Sex
1, mark, peters, mr
2, jane, fonda, mrs
3, john,doo,mr
4, james,bond,mr
Can someone help me with this? I really have no clue how to solve this.
Thanks a lot!
Mike
Mike,
Will there always be exactly 2 commas between each set of curly braces?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2013 at 6:21 pm
Sean Lange (10/9/2013)
erikd (10/9/2013)
dwain.c (10/7/2013)
But it can also be done with a single call to PatternSplitCM:
WITH SampleData(MyString) AS
(
SELECT' [{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
)
SELECT MyString
,Col1=MAX(CASE WHEN ItemNumber%6 = 2 THEN Item END)
,Col2=MAX(CASE WHEN ItemNumber%6 = 4 THEN Item END)
,Col3=MAX(CASE WHEN ItemNumber%6 = 0 THEN Item END)
FROM SampleData a
CROSS APPLY dbo.PatternSplitCM(MyString, '[a-zA-Z]') b
WHERE Matched=1
GROUP BY MyString, (ItemNumber-1)/6;
Is there anything PatternSplitCM CAN'T do? :Wow:
It can't cook waffles or launch pork chops very well. 😛
BWAHAHAHAH!
And it can't split comma delimited strings as fast as DelimitedSplit8K.
However I do like to think there are many creative uses for it:
http://www.sqlservercentral.com/Forums/FindPost1503344.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 9, 2013 at 6:23 pm
Jeff Moden (10/9/2013)
Mike Saunders NL (10/7/2013)
Hello again,I am running into a problem importing a string containing an array into a table.
My string looks like '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
I want to import this into a temp table that looks like:
ID, Fname, Lname, Sex
1, mark, peters, mr
2, jane, fonda, mrs
3, john,doo,mr
4, james,bond,mr
Can someone help me with this? I really have no clue how to solve this.
Thanks a lot!
Mike
Mike,
Will there always be exactly 2 commas between each set of curly braces?
I sense a solution on after-burners in the air.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply