Split a String

Question

Split a String

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
November 21, 2012 at 9:38 am

#267080

Hello Everyone
I hope that everyone is having a great day.
I am working with some horrible data, and I need a little help with one piece of data. The string is this, all of it the same, except for the verbiage of course.
9279-1^Respiratory Rate^LOINC
I need to get that separated, but I do not care about the last section of :
LOINC
The separator is the caret "^", I simply need the data like this:
9279-1
and
Respiratory Rate
I am inserting this data into two separate columns.
I greatly appreciate any and all advice and help with this.
Andrew SQLDBA

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

You should take a look at the 8K Splitter[/url], it can work for you and has a great performance.

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

I have tried that, I was looking for something to simply be used to update a couple columns, but keep the original.

Thanks

Andrew SQLDBA

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

Are you sure? This might give you a better idea.;-)

SELECT string,

MAX( CASE WHEN itemNumber = 1 THEN item END) AS firstpart,

MAX( CASE WHEN itemNumber = 2 THEN item END) AS secondpart

FROM ( SELECT '9279-1^Respiratory Rate^LOINC' string) a

CROSS APPLY dbo.DelimitedSplit8K( a.string, '^') split

GROUP BY 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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Agreed the 8K splitter refrenced by Luis Cazares is great, however for something as simple as your needs, you might want to try the following along with the 8K splitter to determine which performs the best in your situation.

DECLARE @U VARCHAR(100)

SET @U = '9279-1^Respiratory Rate^LOINC'

DECLARE @a INT

DECLARE @b-2 INT

-- To demonstrate what is taking place with neither @a and @b-2 used in the actual solution

SET @a = CHARINDEX('^',@U,1)

SET @b-2 = CHARINDEX('^',@U,@A+1)

-- The following 2 selects are just to demonstrate what is happening

-- SELECT SUBSTRING(@U,1,@A-1) AS 'Answer1',SUBSTRING(@U,@A+1,@B-@A-1)

-- SELECT SUBSTRING(@U,1,CHARINDEX('^',@U,1)-1) AS 'Answer1',SUBSTRING(@U,@A+1,@B-@A-1)

-- After all the demo code the finall answer is:

SELECT SUBSTRING(@U,1,CHARINDEX('^',@U,1)-1) AS 'Answer1',SUBSTRING(@U,@A+1,CHARINDEX('^',@U,@A+1)-@A-1)

Results;

Answer1(No column name)

9279-1Respiratory Rate

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

-- place to test the insert

DECLARE @targetTable TABLE (val1 varchar(30), val2 varchar(30));

-- the value fed to your query

DECLARE @splitMe varchar(40) = '9279-1^Respiratory Rate^LOINC';

;WITH x(a,b) AS

(SELECTLEFT(@splitMe, CHARINDEX('^',@splitMe)-1),

RIGHT(@splitMe, LEN(@splitMe)-CHARINDEX('^',@splitMe))

)

INSERT INTO @targetTable

SELECT a, LEFT(b, CHARINDEX('^',b)-1) FROM x;

SELECT * FROM @targetTable

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

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

bitbucket-25253 (11/21/2012)
Agreed the 8K splitter refrenced by Luis Cazares is great, however for something as simple as your needs, you might want to try the following along with the 8K splitter to determine which performs the best in your situation.

Agreed, it's important to test what's best for each situation.

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

Thanks Everyone

I had to take a little from each query to get what I needed, in a timely manner. I had over 500 million rows to update. But it was very fast.

Andrew SQLDBA

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

AndrewSQLDBA (11/21/2012)
Thanks Everyone
I had to take a little from each query to get what I needed, in a timely manner. I had over 500 million rows to update. But it was very fast.
Andrew SQLDBA

So what did you finally end up with? Can you post the code please?

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