Splitting a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Splitting a string

Author

Topic

knowbody
Starting Member

2 Posts

Posted - 2012-08-24 : 00:07:38

Please forgive me if this has been answered before. I've read several posts online but it seems that most of the discussion is between fairly competent SQL users. I'm pretty new to this and tend to stay in the realm of selects and joins (I've never written a function).

Here's what I'm trying to accomplish

I want to take a string (can be a mixture of alpha, numeric, and special characters) and split the string into separate columns. I also want to use the newly split strings as lookups to associate other values from a table with. I need to be able to pass a string that can be as short as one value or as long as 10.

Ex string Where the colon is the delimiter: 01-Acme B:0038754:NONE:228470312

would return

01-Acme B active Asia
0038754 C Active Europe
NONE B Inactive North America
228470312 A Active North America

As you can see, I've ended up with 4 columns where the first field can be linked to other pertinent data

My problem with many of the solutions I've seen online is that there isn't enough of an explanation for me to be certain I understand why certain things are done.

I would appreciate any information in the right direction.

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-24 : 00:20:38

use fnParseList from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


select Data 
from   dbo.fnParseList (':', @yourstring)

KH
[spoiler]Time is always against us[/spoiler]

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-24 : 03:27:45

Once you've got the data out using the suggestion by khtan , you can manipulate the data . Such as , create JOIN , place in #temp table etc.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

knowbody
Starting Member

2 Posts

Posted - 2012-08-24 : 07:09:00

Thank you khtan and jackv. I will try this out today

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-25 : 21:13:36

While loops in functions make for slow functions and should be avoided at all costs because they are so very slow.

What is the datatype and length of the strings that you're trying to split?

--Jeff Moden

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-26 : 19:56:39

Jeff is too moden[st] to post a link to his own awesome scribblings so I'll do it for him...

When you get a few minutes read this and become enlightened:
http://www.sqlservercentral.com/articles/T-SQL/62867/

it's worth registering for.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-27 : 02:52:15

Gosh... thanks for the kudo, Charlie.

I've got that really fast 8K splitter (from the "Tally OH!" article) but don't have one for the MAX datatypes, yet. There are tons of XML splitters that will handle the MAX datatypes and some of them with pretty good speed.

That's why I asked what datatype the OP is trying to split. I need to know which code to point him to.

--Jeff Moden

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-27 : 07:36:56

quote:
I've got that really fast 8K splitter (from the "Tally OH!" article) but don't have one for the MAX datatypes, yet

I have been wondering about that! I probably have used the Tally-Oh splitter more times than anyone else on the planet and each time I use it I wonder to myself "when is he going to have one for MAX data type"

Jeff, which are the fast splitters for MAX data types that you would recommend?

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-28 : 10:14:35

There's one with a "trick twist" in it that Paul White came up with. I'll post it tonight when I get home.

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-30 : 01:03:13

Apologies for the delay.

This is the code that Paul White posted. So far, it's the fastest XML splitter that I've seen but it still needs to be modified to de-entitize special characters like '&'. For string of 8k or less, the DelimitedSplit8K splitter still beats it by a fairly wide margin.

Still, for MAX datatypes, it's as good as anything I've been able to come up with using cteTally or a Tally table (except for the de-entitization thing. I don't know enough about XML to tell you where to put the TYPE directive).


WITH CTE (xmlstring) AS 
(SELECT CAST('<r>' + REPLACE(@CommaList,',','</r><r>')+'</r>'+LEFT(@@DBTS,0)  AS XML))
 SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 
        Item       = x.i.value('text()[1]', 'varchar(8000)')    
   FROM CTE CROSS APPLY cte.xmlstring.nodes('r') x(i);

--Jeff Moden
{EDIT} Reformatted the code for readability

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-30 : 08:19:11

never like the xml method...

The number table I like and (personally) think can be justified ad 'in db' code

However, given the choice between XML and CLR I would go CLR.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources