October 7, 2011 at 3:31 am
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
October 7, 2011 at 3:42 am
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
October 7, 2011 at 6:05 am
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
October 7, 2011 at 9:52 am
Jason-299789 (10/7/2011)
The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5DThe 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/
October 7, 2011 at 1:01 pm
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
October 7, 2011 at 2:54 pm
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
October 8, 2011 at 2:09 am
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)='#'
October 8, 2011 at 10:23 am
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
Change is inevitable... Change for the better is not.
October 8, 2011 at 10:24 am
Iulian -207023 (10/7/2011)
Thank you so much to all of youI 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
Change is inevitable... Change for the better is not.
October 8, 2011 at 10:26 am
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
Change is inevitable... Change for the better is not.
October 8, 2011 at 10:28 am
Jason-299789 (10/7/2011)
The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5DThe 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
Change is inevitable... Change for the better is not.
October 8, 2011 at 10:30 am
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
Change is inevitable... Change for the better is not.
October 8, 2011 at 2:07 pm
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
October 8, 2011 at 3:26 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply