Extract string between to characters

Question

Extract string between to characters

Trestire

SSC Enthusiast

Points: 129
More actions
September 13, 2013 at 10:09 am

#283200

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!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 2

It's not really clear to me what the result should look like - what exactly belongs in each column?

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

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/

Trestire SSC Enthusiast Points: 129 More actions · Answer 4

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!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

Trestire SSC Enthusiast Points: 129 More actions · Answer 6

After I replied the other two I saw your comment.

I just finished the reading and I'm working now on it!

Thank again 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

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, '><', '|'), '<', ''), '>', ''), '|')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

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/

ccavaco Old Hand Points: 302 More actions · Answer 11

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@String, '<', ' '), '>', ''), '|', ' ')))

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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|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!

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)