String to rows

Question

Post reply

String to rows

Iulian -207023

SSCertifiable

Points: 7639
More actions
October 7, 2011 at 3:31 am

#249023

Hello,
How can I parse a string into rows?
Ex: from_ text = "A # B # C"
I need to get_:
Col_Name
------------
A
B
C
Thank you,
Iulian

Viewing 15 posts - 1 through 15 (of 19 total)

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5D

The only difference is that instead of using a comma seperated list you use a # delimiter.

One minor tweak could be to change the function to accept a Delimiter so that its configurable depending on your needs.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 2

Try:

DECLARE @xml xml, @v-2 nvarchar(max)

SELECT @v-2 = N'A # B # C'

SET @xml = N'<root><r>' + replace(@v,'#','</r><r>') + '</r></root>'

SELECT

replace(a.value('.','varchar(2)'),' ','') AS [RowName]

FROM @xml.nodes('//root/r') AS a(a)

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 3

thanks carolyn, its really a nice solution i use to do it via UDF.

i think in term of performance also this one is better.

thanks

ghanshyam

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

Jason-299789 (10/7/2011)
The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5D
The only difference is that instead of using a comma seperated list you use a # delimiter.
One minor tweak could be to change the function to accept a Delimiter so that its configurable depending on your needs.

Instead of using a loop based solution you should instead use a set based solution for this. Our very won Jeff Moden has a fantastic delimited string splitter. You can fit it here. http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]. The link you pointed to is the more typical loop based approach. It works ok on small datasets but is totally crippled with large datasets.

_______________________________________________________________

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/

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 5

Thank you so much to all of you

I think I will try both methods

the xml method to parse the file into lines and push to a table

then the CLR method eith regular expressions to parse each line into fields

Kind regards,

Iulian

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Faster would be to use the Split function by Jeff Moden already mentioned or to use a CLR splitter that you can find in the discussion on the link provided by Sean.

If you need to break those rows into columns, why not use the pivot command?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

yubo1 SSChasing Mays Points: 612 More actions · Answer 7

create table test

(

string varchar(max)

)

insert into test

select 'A#B#C'

select SUBSTRING(string+'#',number,charindex('#',string+'#',number)-number) from

test,

(select number from master..spt_values where number>=1 and type='P')t

where SUBSTRING('#'+string,number,1)='#'

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

yubo1 (10/8/2011)
create table test ( string varchar(max) ) insert into test select 'A#B#C' select SUBSTRING(string+'#',number,charindex('#',string+'#',number)-number) from test, (select number from master..spt_values where number>=1 and type='P')t
where SUBSTRING('#'+string,number,1)='#'

Yubo1,

That method is the "old" method of splitting that uses concatenated delimiters. It's very fast for a small number of elements in the string but rapidly degrades with string length. Please see the following article for why you shouldn't use the method you posted and a much faster (closest yet to a decent CLR) methods as an alternative.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

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

Iulian -207023 (10/7/2011)
Thank you so much to all of you
I think I will try both methods
the xml method to parse the file into lines and push to a table
then the CLR method eith regular expressions to parse each line into fields
Kind regards,
Iulian

Nope... I recommend that don't use the XML method... it's almost as slow as a While Loop. Please see the following article for proof of that and the recommended "new" alternative.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

Carolyn Richardson (10/7/2011)
Try:
DECLARE @xml xml, @v-2 nvarchar(max)
SELECT @v-2 = N'A # B # C'
SET @xml = N'<root><r>' + replace(@v,'#','</r><r>') + '</r></root>'
SELECT
replace(a.value('.','varchar(2)'),' ','') AS [RowName]
FROM @xml.nodes('//root/r') AS a(a)

Hi Carolyn,

Please see the following article for the reasons why I recommend you don't use the XML method of splitting and the relatively new method of splitting whose speed is rivaled only by a well written CLR.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

Jason-299789 (10/7/2011)
The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5D
The only difference is that instead of using a comma seperated list you use a # delimiter.
One minor tweak could be to change the function to accept a Delimiter so that its configurable depending on your needs.

Jason,

The method in that article is what is known as a "Nibbler". Please see the following article for why that method probably shouldn't be used and what the new, high performance method of splitting is...

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

ghanshyam.kundu (10/7/2011)
thanks carolyn, its really a nice solution i use to do it via UDF.
i think in term of performance also this one is better.
thanks
ghanshyam

But, it's not. Please see the following article for a relatively new, high speed splitter that uses an iTVF (Inline Table Valued Function) instead of a relatively slow mTVF (Multi-Line Table Valued Function).

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 13

Thank you all,

Do you have at hand a sample code on how to create the CLR function for splitting a string into rows / fields? I am not familiar with CLR functions, yet 🙂

I need to parse each line using regular expression, at least this is what I have in mind now.

After reading your recommendations I have the feeling regular expression using CLR is the key of my exercise. I appreciate any help / sample on this.

A sample:

If the input is:

A # 2 # 09/20/2011

X # 09/20/2011 & 123.00

A # 3 # 10/07/2011

the result will have 3 rows, each row having 4 fields:

Record_Name ID Date Value

A 2 09/20/2011 0

X 0 09/20/2011 123.00

A 3 10/07/2011 0

Thanks,

Iulian

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 14

Iulian -207023 (10/8/2011)
Thank you all,
Do you have at hand a sample code on how to create the CLR function for splitting a string into rows / fields? I am not familiar with CLR functions, yet 🙂
I need to parse each line using regular expression, at least this is what I have in mind now.
After reading your recommendations I have the feeling regular expression using CLR is the key of my exercise. I appreciate any help / sample on this.
A sample:
If the input is:
A # 2 # 09/20/2011
X # 09/20/2011 & 123.00
A # 3 # 10/07/2011
the result will have 3 rows, each row having 4 fields:
Record_Name ID Date Value
A 2 09/20/2011 0
X 0 09/20/2011 123.00
A 3 10/07/2011 0
Thanks,
Iulian

You need to read the code attached to the article I pointed you to... it's in there. And, no... using regular expressions for split probably won't be faster than a CLR written specifically to do splits.

Personally, I think enabling CLR just for the sake of a splitter is overkill. If it's already enabled, the the CLR splitter code I've just pointed you to will do the job nicely. You do have to make up your mind what you want to use for a delimiter, though. 😉

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