February 6, 2014 at 10:55 am
Hi,
I have a variable of 40 characters length which contains a complete string. I need to split the string based on pattern
For example i have a string which contains test1##test2##test3
I need to split this string as
test1
test2
test3
Please suggest easiest way to do this as i have tried with substring and charindex functions
February 6, 2014 at 11:16 am
vikramchander90 (2/6/2014)
Hi,I have a variable of 40 characters length which contains a complete string. I need to split the string based on pattern
For example i have a string which contains test1##test2##test3
I need to split this string as
test1
test2
test3
Please suggest easiest way to do this as i have tried with substring and charindex functions
You should take a look at the article in my signature about splitting strings.
In this case your delimiter is 2 characters so you have a couple of options.
declare @String varchar(40) = 'test1##test2##test3'
select *
from dbo.DelimitedSplit8K(@String, '#')
where Item > ''
Or if you can be certain there are no instances of a single '#' in your data you could do it like this.
set @String = REPLACE(@String, '##', '#')
select *
from dbo.DelimitedSplit8K(@String, '#')
_______________________________________________________________
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/
February 6, 2014 at 11:22 am
Another option would be to use Dwain Camps' pattern based splitter.
http://www.sqlservercentral.com/articles/String+Manipulation/94365/[/url]
select *
from dbo.PatternSplitCM('test1##test2##test3', '%#%')
where Matched = 0
_______________________________________________________________
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/
February 6, 2014 at 11:31 am
Sean Lange (2/6/2014)
Another option would be to use Dwain Camps' pattern based splitter.
That splitter is made to split string when the delimiter is not a constant, because it won't perform as well as the DelimitedSplit8K.
If your data might contain a # that's not a delimiter, you could use Sean's first advice but change the delimiter to a different character that won't be in your string (maybe CHAR(7) or something similar) and split by that one.
It would end like this:
set @String = REPLACE(@String, '##', CHAR(7))
select *
from dbo.DelimitedSplit8K(@String, CHAR(7))
February 6, 2014 at 11:32 am
Luis Cazares (2/6/2014)
Sean Lange (2/6/2014)
Another option would be to use Dwain Camps' pattern based splitter.That splitter is made to split string when the delimiter is not a constant, because it won't perform as well as the DelimitedSplit8K.
If your data might contain a # that's not a delimiter, you could use Sean's first advice but change the delimiter to a different character that won't be in your string (maybe CHAR(7) or something similar) and split by that one.
It would end like this:
set @String = REPLACE(@String, '##', CHAR(7))
select *
from dbo.DelimitedSplit8K(@String, CHAR(7))
Agreed I was merely pointing it out as a possible alternative. 😛
_______________________________________________________________
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/
February 6, 2014 at 11:39 am
Even if the article is not related directly with this problem, it gave me the idea for the CHAR(7) option. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply