September 13, 2013 at 10:09 am
I've a string in following format:
<1|458|test|q><2|789|test2|s><5|567|test3|p>
First I've to extract the strings between < and >
like: 1|458|test|q 2|789|test2|s 5|567|test3|p
after that I've to split the the pipes: 1 458 test q 2789 test2 s 5 567 test3 p
The occurrences of <> is not set to a specific number. I want to present the result in one row in different columns.
I've tried with different functions (charindex, instring, substring) but no success the way I need the data.
Any suggestions? Thanks in advance!
September 13, 2013 at 10:24 am
Sure no problem. What you need to do is parse your string and then parse the results. Parsing strings in sql is the challenge here. Take a look at the link in my signature about splitting strings. In there you will find code for the DelimitedSplit8K function. Make sure you understand what it is doing.
Your code will end up something like this.
declare @String varchar(100) = '<1|458|test|q><2|789|test2|s><5|567|test3|p>'
select MAX(case when x.ItemNumber = 1 then x.Item end) as Col1,
MAX(case when x.ItemNumber = 2 then x.Item end) as Col2,
MAX(case when x.ItemNumber = 3 then x.Item end) as Col3,
MAX(case when x.ItemNumber = 4 then x.Item end) as Col4
from
(
select *
from dbo.DelimitedSplit8K(replace(@String, '>', ''), '<')
where Item > ''
) FirstSplit
cross apply dbo.DelimitedSplit8K(Item, '|') x
group by FirstSplit.ItemNumber
_______________________________________________________________
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/
September 13, 2013 at 10:25 am
It's not really clear to me what the result should look like - what exactly belongs in each column?
September 13, 2013 at 11:09 am
your requirement is not clear to us..can you please provide some more detail information what you exactly want?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 11:27 am
First of all thanks for your time.
I'll paraphrase my post:
I've a string in following format. I've this string in my a table column:
<1|458|test|q><2|789|test2|s><5|567|test3|p>
First I've to extract the strings between < and >. I want to get a substring of my string without '<' and '>'
so this:
1|458|test|q 2|789|test2|s 5|567|test3|p
after that I've to get the values without '|': 1 458 test q 2789 test2 s 5 567 test3 p
The occurrences of <> is not set to a specific number. I want to present the result in one row in different columns.
Basically all values between the | needs to go in a separate column. SO in my case I should have 12 columns to store each separate value between the | in a separate column.
Maybe my suggestion that I've to split the character first by omitting the < and > is wrong? Because the data between < and > belongs to one class it's being stored between those special characters.
Hope I was a bit more clear now.
Thanks again for your comments.
I've tried with different functions (charindex, instring, substring) but no success the way I need the data.
Any suggestions? Thanks in advance!
September 13, 2013 at 12:14 pm
I was pretty clear to me what you wanted. Did you try reading the article? It produces exactly what you stated you want as output.
_______________________________________________________________
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/
September 13, 2013 at 12:19 pm
After I replied the other two I saw your comment.
I just finished the reading and I'm working now on it!
Thank again 🙂
September 13, 2013 at 12:20 pm
Maybe you're trying to hard with different functions and didn't notice the splitter that Sean commented. With some replaces, it should work great for you.
SELECT MAX( CASE WHEN ItemNumber = 1 THEN Item END) Col1 ,
MAX( CASE WHEN ItemNumber = 2 THEN Item END) Col2,
MAX( CASE WHEN ItemNumber = 3 THEN Item END) Col3,
MAX( CASE WHEN ItemNumber = 4 THEN Item END) Col4,
MAX( CASE WHEN ItemNumber = 5 THEN Item END) Col5,
MAX( CASE WHEN ItemNumber = 6 THEN Item END) Col6,
MAX( CASE WHEN ItemNumber = 7 THEN Item END) Col7,
MAX( CASE WHEN ItemNumber = 8 THEN Item END) Col8,
MAX( CASE WHEN ItemNumber = 9 THEN Item END) Col9,
MAX( CASE WHEN ItemNumber = 10 THEN Item END) Col10,
MAX( CASE WHEN ItemNumber = 11 THEN Item END) Col11,
MAX( CASE WHEN ItemNumber = 12 THEN Item END) Col12
FROM (VALUES('<1|458|test|q><2|789|test2|s><5|567|test3|p>')) x(String)
CROSS APPLY dbo.DelimitedSplit8K( REPLACE(REPLACE(REPLACE( String, '><', '|'), '<', ''), '>', ''), '|')
September 15, 2013 at 7:11 pm
Sean Lange (9/13/2013)
Sure no problem. What you need to do is parse your string and then parse the results. Parsing strings in sql is the challenge here. Take a look at the link in my signature about splitting strings. In there you will find code for the DelimitedSplit8K function. Make sure you understand what it is doing.Your code will end up something like this.
declare @String varchar(100) = '<1|458|test|q><2|789|test2|s><5|567|test3|p>'
select MAX(case when x.ItemNumber = 1 then x.Item end) as Col1,
MAX(case when x.ItemNumber = 2 then x.Item end) as Col2,
MAX(case when x.ItemNumber = 3 then x.Item end) as Col3,
MAX(case when x.ItemNumber = 4 then x.Item end) as Col4
from
(
select *
from dbo.DelimitedSplit8K(replace(@String, '>', ''), '<')
where Item > ''
) FirstSplit
cross apply dbo.DelimitedSplit8K(Item, '|') x
group by FirstSplit.ItemNumber
Luis Cazares (9/13/2013)
Maybe you're trying to hard with different functions and didn't notice the splitter that Sean commented. With some replaces, it should work great for you.
SELECT MAX( CASE WHEN ItemNumber = 1 THEN Item END) Col1 ,MAX( CASE WHEN ItemNumber = 2 THEN Item END) Col2,
MAX( CASE WHEN ItemNumber = 3 THEN Item END) Col3,
MAX( CASE WHEN ItemNumber = 4 THEN Item END) Col4,
MAX( CASE WHEN ItemNumber = 5 THEN Item END) Col5,
MAX( CASE WHEN ItemNumber = 6 THEN Item END) Col6,
MAX( CASE WHEN ItemNumber = 7 THEN Item END) Col7,
MAX( CASE WHEN ItemNumber = 8 THEN Item END) Col8,
MAX( CASE WHEN ItemNumber = 9 THEN Item END) Col9,
MAX( CASE WHEN ItemNumber = 10 THEN Item END) Col10,
MAX( CASE WHEN ItemNumber = 11 THEN Item END) Col11,
MAX( CASE WHEN ItemNumber = 12 THEN Item END) Col12
FROM (VALUES('<1|458|test|q><2|789|test2|s><5|567|test3|p>')) x(String)
CROSS APPLY dbo.DelimitedSplit8K( REPLACE(REPLACE(REPLACE( String, '><', '|'), '<', ''), '>', ''), '|')
I don't recommend doing the REPLACE in the call to DelimitedSplit8K function. Even though that is an awesome tool, I've run into some unexpected performance issues doing that sort of embedded REPLACE.
I've gotten better results introducing an extra CROSS APPLY before the function call like this (replace last line in Luis's solution:
CROSS APPLY (SELECT str=REPLACE(REPLACE(REPLACE( String COLLATE latin1_general_bin, '><', '|'), '<', ''), '>', '')) x
CROSS APPLY dbo.DelimitedSplit8K( str, '|')
Unfortunately, I am not in a position at this time to prove this assertion for various reasons.
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
September 15, 2013 at 8:30 pm
Trestire (9/13/2013)
I've a string in following format:<1|458|test|q><2|789|test2|s><5|567|test3|p>
First I've to extract the strings between < and >
like: 1|458|test|q 2|789|test2|s 5|567|test3|p
after that I've to split the the pipes: 1 458 test q 2789 test2 s 5 567 test3 p
The occurrences of <> is not set to a specific number. I want to present the result in one row in different columns.
I've tried with different functions (charindex, instring, substring) but no success the way I need the data.
Any suggestions? Thanks in advance!
Will each "element" ALWAYS have only 3 pipes in it?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2013 at 7:48 am
dwain.c (9/15/2013)
I don't recommend doing the REPLACE in the call to DelimitedSplit8K function. Even though that is an awesome tool, I've run into some unexpected performance issues doing that sort of embedded REPLACE.
I've gotten better results introducing an extra CROSS APPLY before the function call like this (replace last line in Luis's solution:
CROSS APPLY (SELECT str=REPLACE(REPLACE(REPLACE( String COLLATE latin1_general_bin, '><', '|'), '<', ''), '>', '')) x
CROSS APPLY dbo.DelimitedSplit8K( str, '|')
Unfortunately, I am not in a position at this time to prove this assertion for various reasons.
Interesting Dwain. I have not run into issue or remember hearing of it before. If I get some time I may toss some performance tests at this myself.
_______________________________________________________________
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/
September 16, 2013 at 3:29 pm
SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@String, '<', ' '), '>', ''), '|', ' ')))
September 16, 2013 at 6:29 pm
Sean Lange (9/16/2013)
dwain.c (9/15/2013)
I don't recommend doing the REPLACE in the call to DelimitedSplit8K function. Even though that is an awesome tool, I've run into some unexpected performance issues doing that sort of embedded REPLACE.
I've gotten better results introducing an extra CROSS APPLY before the function call like this (replace last line in Luis's solution:
CROSS APPLY (SELECT str=REPLACE(REPLACE(REPLACE( String COLLATE latin1_general_bin, '><', '|'), '<', ''), '>', '')) x
CROSS APPLY dbo.DelimitedSplit8K( str, '|')
Unfortunately, I am not in a position at this time to prove this assertion for various reasons.
Interesting Dwain. I have not run into issue or remember hearing of it before. If I get some time I may toss some performance tests at this myself.
I did some tests on it once. Unfortunately, I didn't save that test harness. Recreating it is kind of a pain because you need to make sure that you have a considerable amount of randomness in your test data to generate a proper test scenario.
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
September 16, 2013 at 6:37 pm
Trestire (9/13/2013)
First of all thanks for your time.I'll paraphrase my post:
I've a string in following format. I've this string in my a table column:
<1|458|test|q><2|789|test2|s><5|567|test3|p>
First I've to extract the strings between < and >. I want to get a substring of my string without '<' and '>'
so this:
1|458|test|q 2|789|test2|s 5|567|test3|pafter that I've to get the values without '|': 1 458 test q 2789 test2 s 5 567 test3 p
The occurrences of <> is not set to a specific number. I want to present the result in one row in different columns.
Basically all values between the | needs to go in a separate column. SO in my case I should have 12 columns to store each separate value between the | in a separate column.
Maybe my suggestion that I've to split the character first by omitting the < and > is wrong? Because the data between < and > belongs to one class it's being stored between those special characters.
Hope I was a bit more clear now.
Thanks again for your comments.
I've tried with different functions (charindex, instring, substring) but no success the way I need the data.
Any suggestions? Thanks in advance!
It's still not clear what you actually want the result set to be. Should your example of <1|458|test|q><2|789|test2|s><5|567|test3|p> come out to be 3 rows with 3 columns per row or do you want it to just be a large string without the <|> symbols or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply