Splitting a String in SQL - Help Please!

Question

Post reply

Splitting a String in SQL - Help Please!

greig.burrows

SSC Rookie

Points: 37
More actions
September 12, 2008 at 9:10 am

#71087

Hi there! I'm struggling to split a string.. hope somebody can help me. Here's a description of my problem:
CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door
I need to access the first two parts of the above string.
CSCAR009/0001/001
PASPBD
I can use left() to get at the first part. It is the same length in all rows.
However this cannot be said for the second. It is a code which changes from row to row, and isn't the same length.
The description on the right also changes and doesn't share the same format even with the same code (user inputted field). I am not interested in this field.
I have tried using the substring function but the third part of the string pops in with the second which messes me up.
I believe what I need is to use a split command on the string to get access to the first 2 parts. What I want is to assign these parts to seperate variables in ASP (the SQL statement will be ran from there too) enabling me to use the data.
I found a guide on inserting a User defined Split function, but am at a loss on how to use it for my purpose. The examples given only show using it in a where clause with an IN performed.
UDF Split:
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
I rarely post on forums asking for help but this problem has eaten up my afternoon.
Thank you in advance for any help offered.

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

If the -'s are there in every string, you can use CHARINDEX to extract the middle piece.

DECLARE @String varchar(60)

SET @String = 'CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door'

SELECT LEFT(RIGHT(@String,LEN(@String)-18),(CHARINDEX('-',RIGHT(@String,LEN(@String)-18) ) )-1)

The best thing about this method is that it can be used in any query, replacing @string with your field name.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Convert table split function to scalar one.

Add another parameter - @ElementNo and return only value from the table within function with correlated ID.

The you may use it like this:

SELECT dbo.SplitScalar(StringValue, '-', 1), dbo.SplitScalar(StringValue, '-', 2)

FROM ....

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

greig.burrows (9/12/2008)
Hi there! I'm struggling to split a string.. hope somebody can help me. Here's a description of my problem:

Ugh... UDF's... While Loops... all good ways to slow your code down. Before I show you how to do this, I need to know a couple of things so I can decide which method to show you...

1. Are you just splitting a single parameter (Sergiy is on the right track for this), or are you splitting a whole column of these?

2. If you're splitting a whole column, I need to know what the primary key of the table is. It would also be handy to have more than one piece of data. Please take a look at the link in my signature below on how to correctly list the data to make it easier for me to help you.

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 4

I have a library of UDfs that do exactly what you are looking for and much, much more. however, I sense your aversion to UDFs so I am not sure how you want to go.

There was an article here in SQL Server Central a while ago about "Tally Tables" Using a "Tally" or numbers table will get you where you want to go without loops or UDFs.

Respond back if you want to check out my UDFs.

-M

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Funny you should bring that article up... 🙂

http://www.sqlservercentral.com/articles/TSQL/62867/

But the desired method to do the split depends on my question... Is this a single parameter being split or does a whole column need to be split?

--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: 1004177 More actions · Answer 6

Oh hell... so long as I'm being self-indulgent...

[font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font]

http://www.sqlservercentral.com/articles/T-SQL/63003/

But, what I'd really like to know is what I've asked a couple of times now... is this to split a single parameter or a whole column of information? The performance of the solution is dependent on that, a lot.

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 7

No doubt RBAR is going to occur with UDFs. The tally table idea is going to be a join. The UDF will defintely work. So what do you want? Pretty or Guts & Glory?

-M

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

Performance 🙂

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

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Jeff, performance is good.

But did you see a solution for splitting string with quoted values?

Like in CSV files?

_____________
Code for TallyGenerator

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

Absolutely... what do you want to do? Split a true CSV parameter or a tru CSV column in a poorly formed table that needs a bit of normalization? Heh... or do you want to import a properly formed true CSV file?

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 11

Use the Tally table, join to it, then use intrisinc SQL functions because you will have postive indexes to work with. Good luck.

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

Mike DiRenzo (9/13/2008)
Use the Tally table, join to it, then use intrisinc SQL functions because you will have postive indexes to work with. Good luck.

Who's that directed to, Mike?

--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: 1004177 More actions · Answer 13

Greig,

When you get a chance... all I need to know is are you splitting a single parameter or do you need to split a whole column of these? If a whole column, then I need to know what the PK of the table is, as well.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 14

Jeff Moden (9/13/2008)
Absolutely... what do you want to do? Split a true CSV parameter or a tru CSV column in a poorly formed table that needs a bit of normalization? Heh... or do you want to import a properly formed true CSV file?

I've got set of rows coming from "a properly formed true CSV file".

Something like this:

'"Pick-up",,"",1234,"2008-08-24", "2008-08-24","17"", unknown brand"'

'"""Next Day"" delivery",5.00,"9,5"" box",10,"2008-09-01", "2008-09-11","9"" nails"'

You understand, it must come to (for 2nd line):

"Next Day" delivery

5.00

9,5" box

10

2008-09-01

"2008-09-11"

9" nails

_____________
Code for TallyGenerator