Splitting A String

Question

Post reply

Splitting A String

Goldie Lesser

SSCertifiable

Points: 6155
More actions
March 15, 2010 at 4:24 pm

#230870

Hi,
I have outline numbers such as I.A.1.b.
I am trying to split them into a string as follows I., I.A., I.A.1., I.A.1.b.
Here's what I have so far:
DECLARE @OutlineNumber VARCHAR(1000) = '.I.A.1.b'
SELECT SUBSTRING(@OutlineNumber,1,CHARINDEX('.',@OutlineNumber,N+1))
FROM dbo.Tally
WHERE N < LEN(@OutlineNumber)
AND SUBSTRING(@OutlineNumber,N,1) = '.'

Viewing 13 posts - 1 through 12 (of 12 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

DECLARE @OutlineNumber VARCHAR(1000)

SET @OutlineNumber= '.I.A.1.b.'

SELECT STUFF(SUBSTRING(@OutlineNumber,1,CHARINDEX('.',@OutlineNumber,N+1)),1,1,'')

FROM dbo.Tally

WHERE N < LEN(@OutlineNumber)

AND SUBSTRING(@OutlineNumber,N,1) = '.'

The variable needs to have a dot at the beginning and at the then for this solution to work...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jarjarlee SSC Enthusiast Points: 102 More actions · Answer 2

You should use a funcation.

define a funcation like

CREATE FUNCTION [dbo].[SplittingByDot]
(
@SourceData varchar(255)
)
RETURNS NVARCHAR
AS
BEGIN
......
END

then use the

SELECT dbo.SplittingByDot(OutlineNumber) FROM dbo.Tally

get the result

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

jarjarlee (3/15/2010)
You should use a funcation.
define a funcation like
CREATE FUNCTION [dbo].[SplittingByDot]
(
@SourceData varchar(255)
)
RETURNS NVARCHAR
AS
BEGIN
......
END
then use the
SELECT dbo.SplittingByDot(OutlineNumber) FROM dbo.Tally
get the result

Other than writing a CLR, it would a lot faster to write an iTVF (Inline Table Valued Function) and then use CROSS APPLY to reference it.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

If you're going to run this against sets of rows, consider writing it as an inline table-valued funcation.

CREATE FUNCTION dbo.tfn_DotSplitFuncation

(

@OutlineNumber VARCHAR(1000)

)

RETURNS TABLE

AS

RETURN

(

with cte (outlineNumber) as (select replace(replace('.^.','^',@outlineNumber),'..','.'))

SELECT STUFF(SUBSTRING(OutlineNumber,1,CHARINDEX('.',OutlineNumber,N+1)),1,1,'') AS OutlineNumber

FROM dbo.Tally

CROSS JOIN CTE

WHERE N < LEN(OutlineNumber)

AND SUBSTRING(OutlineNumber,N,1) = '.'

)

GO

/* test

-- get down, get funky

;with test (oln) as

(select 'I.A.2.g' union all

select 'IV.C.12,a' union all

select 'XIII.B.1,c,ii'

)

select *

from test

cross apply dbo.tfn_DotSplitFuncation(oln)

*/

Lutz, I stole your excellent code and just added a wrinkle to make starting and ending periods irrelevant.

Now pardon me, while I show my age....

"Awwww we want the func.... give up the func.... aww we need the func... gotta have that func!!"

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Jeff you were posting while I was funckifying.... 😛

Congrats on the 20k. They should create a Golden Pork Chop award and pass it on to future recipients like the Lombardi Trophy.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 6

Hi Goldie,

The following script is giving the outcome you want in my test database.

As you will see there is a string split function within the first part of the sql

Then a concetenation takes place in the second part.

You can find reference documents about the code for sql split string function and sql concatenation function

DECLARE @OutlineNumber VARCHAR(1000) = '.I.A.1.b';

with cte as (

select rn=ROW_NUMBER() over (order by id), val

from dbo.split(@OutlineNumber,'.')

where len(val ) > 0

)

select

STUFF

(

SELECT

'.' + c.val

FROM cte c

WHERE c.rn <= cte.rn

FOR XML PATH('')

), 1, 1, ''

) As concatenated_string

from cte

I hope that helps,

Eralper

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Paul White SSC Guru Points: 150467 More actions · Answer 7

Eralper (3/16/2010)
You can find reference documents about the code for sql split string function

Yikes. XML is not the right tool for the job when it comes to string splitting.

The tally-table method presented by Lutz, Bob, and Jeff is far, far, superior.

Almost as good a a CLR string splitting function in fact.

Comprehensive test results: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

lmu92 (3/15/2010)
Something like this?
DECLARE @OutlineNumber VARCHAR(1000)
SET @OutlineNumber= '.I.A.1.b.'
SELECT STUFF(SUBSTRING(@OutlineNumber,1,CHARINDEX('.',@OutlineNumber,N+1)),1,1,'')
FROM dbo.Tally
WHERE N < LEN(@OutlineNumber)
AND SUBSTRING(@OutlineNumber,N,1) = '.'
The variable needs to have a dot at the beginning and at the then for this solution to work...

This is exactly what I was looking for!

Thank you all for your help.

LutzM SSC Guru Points: 107049 More actions · Answer 9

Paul White (3/16/2010)
...
Yikes. XML is not the right tool for the job when it comes to string splitting.
The tally-table method presented by Lutz, Bob, and Jeff is far, far, superior.
Almost as good a a CLR string splitting function in fact.
Comprehensive test results: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html
Paul

I have to object!

Not because of the method used but because of giving me credit for it in this case.

Actually, Goldie already used the Tally solution. I just added the STUFF() part, which has been further improved by "Dixie-Bob".

So, Dixie, you haven't stolen any kind of excellent code as far as I'm concerned. Credit belongs to Goldie.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Paul White (3/16/2010)
The tally-table method ... {snip} ... is far, far, superior.
Almost as good a a CLR string splitting function in fact.

Heh... keepsake that is right thar...

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

Paul White SSC Guru Points: 150467 More actions · Answer 11

lmu92 (3/16/2010)
I have to object! Not because of the method used but because of giving me credit for it in this case.
Actually, Goldie already used the Tally solution. I just added the STUFF() part, which has been further improved by "Dixie-Bob".
So, Dixie, you haven't stolen any kind of excellent code as far as I'm concerned. Credit belongs to Goldie.

Ha, OK! 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

Jeff Moden (3/16/2010)
Paul White (3/16/2010)
The tally-table method ... {snip} ... is far, far, superior.
Almost as good a a CLR string splitting function in fact.
Heh... keepsake that is right thar...

:laugh: Well, it is true!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi