How to delimit ‘-’ string?

Question

How to delimit ‘-’ string?

jan michael woo cerna-336610

Right there with Babe

Points: 724
More actions
January 22, 2009 at 7:11 am

#196314

Hi all:
Given: ‘111111-0000-9999’
Return:
SeqResult
1111111
20000
39999
Do we have a function in sql2k5 that will delimit ‘-’ given the scenario ?
Thanks,
JMC

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Numerous udf's have been posted, search under split string, e.g. http://www.sqlservercentral.com/scripts/Miscellaneous/31913/

This[/url] excellent article shows how to do it with a tally table.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 2

There are no built-in SQL server functions that handle this. You have to write this yourself although this problem has been solved in many ways as Chris pointed out earlier.

--Creates an 'InLine' Table Valued Function (TVF)

CREATE FUNCTION dbo.Split

( @Delimiter varchar(5),

@List varchar(8000)

)

RETURNS @TableOfValues table

( RowID smallint IDENTITY(1,1),

[Value] varchar(50)

)

AS

BEGIN

DECLARE @LenString int

WHILE len( @List ) > 0

BEGIN

SELECT @LenString =

(CASE charindex( @Delimiter, @List )

WHEN 0 THEN len( @List )

ELSE ( charindex( @Delimiter, @List ) -1 )

END

)

INSERT INTO @TableOfValues

SELECT substring( @List, 1, @LenString )

SELECT @List =

(CASE ( len( @List ) - @LenString )

WHEN 0 THEN ''

ELSE right( @List, len( @List ) - @LenString - 1 )

END

)

END

RETURN

END

go

SELECT *

FROM dbo.Split( '-', '111111-0000-9999' ) AS s

ORDER BY s.[Value]

The above split function is dervied from Jens Suessmeyer's split function.

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

jan michael woo cerna-336610 Right there with Babe Points: 724 More actions · Answer 3

Thanks Sankar and Chris for your replies. It helps my project. 🙂

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Sankar Reddy (1/24/2009)
There are no built-in SQL server functions that handle this. You have to write this yourself although this problem has been solved in many ways as Chris pointed out earlier.
--Creates an 'InLine' Table Valued Function (TVF)
CREATE FUNCTION dbo.Split
( @Delimiter varchar(5),
@List varchar(8000)
)
RETURNS @TableOfValues table
( RowID smallint IDENTITY(1,1),
[Value] varchar(50)
)
AS
BEGIN
DECLARE @LenString int
WHILE len( @List ) > 0
BEGIN
SELECT @LenString =
(CASE charindex( @Delimiter, @List )
WHEN 0 THEN len( @List )
ELSE ( charindex( @Delimiter, @List ) -1 )
END
)
INSERT INTO @TableOfValues
SELECT substring( @List, 1, @LenString )
SELECT @List =
(CASE ( len( @List ) - @LenString )
WHEN 0 THEN ''
ELSE right( @List, len( @List ) - @LenString - 1 )
END
)
END
RETURN
END
go
SELECT *
FROM dbo.Split( '-', '111111-0000-9999' ) AS s
ORDER BY s.[Value]
The above split function is dervied from Jens Suessmeyer's split function.

Consider avoiding the loop for performance sake...

[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]

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

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 5

Jeff is right. If the data set is large then you are better off with Numbers table approach. But if you are doing small data sets or just a variable inside a SP then you can use the split function. I am demonstrating one of the techniques that can be used to solve this.

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

There are no guarantees that what you're doing will stay small and there are no guarantees that someone won't look at that RBAR code and decide it's ok for them to use on something big. Never use a while loop for such a thing. Never justify RBAR in any way, shape, or form. RBAR is no easier to render than good set based code and like Polio in the U.S. it should be wiped out because of it's crippling effect on databases.

Saying you can use RBAR if the number of rows will stay small is like saying it's ok to come down with Polio if you're sure you're not going to be a marathon runner. 😉

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

jan michael woo cerna-336610 Right there with Babe Points: 724 More actions · Answer 7

hi jeff, thanks for the adviced. I'm working on a large data for a particular client. Are there any other options w/o using a while loop? how am i going to scan other data? there are other articles I read that its better to use while loop than using cursor.

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

jan michael woo cerna (1/25/2009)
hi jeff, thanks for the adviced. I'm working on a large data for a particular client. Are there any other options w/o using a while loop? how am i going to scan other data? there are other articles I read that its better to use while loop than using cursor.

First, it's a myth that a While Loop is better than a cursor. A good "fire hose" cursor (static, forward only or forward only, read only) is every bit as effective as an equivalent temp table and While Loop. The proof has been done many, many times. And, in the cases where a While Loop does actually beat a cursor, it's by such a small margin when compared to a set based solution, that it doesn't really matter.

Yes, there are lot's of options to overcoming loops... proper joins, variable overlays, Tally table usage (a type of proper join), and just plain old good database programming practices. And, just because something doesn't have an explicit loop or cursor in it, doesn't necessarily mean it's set based. For example, triangular joins are thousands of times worse than cursors and recursive CTE's are also worse than cursors in most cases.

If you want to read some articles on how to beat loops, take a look at my collection of article. Do understand that the article on "running totals" has a bit of bad information in it in that you cannot actually use an index hint on a SELECT to replace an ORDER BY and that article is in the process of being rewritten. The "quirky" UPDATE in that article, however, is spot on.

Here's a link to all of my articles... start at the "bottom" with the "Hidden RBAR" article, and work your way "up"...

http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/

By the way... in the Tally Table article (the article, not the script), you'll find a method for splitting a whole table's worth of a delimited column... in one very high speed SELECT and without the use of a UDF...

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

jan michael woo cerna-336610 Right there with Babe Points: 724 More actions · Answer 9

Thanks again Jeff. I will take time to read and study your articles. 🙂