SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Splitting A String


Splitting A String

Author
Message
Goldie Lesser
Goldie Lesser
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 1501
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) = '.'



LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10379 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
jarjarlee
jarjarlee
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86724 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5442 Visits: 6900
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
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5442 Visits: 6900
Jeff you were posting while I was funckifying.... :-P

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
Eralper
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 466
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
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15818 Visits: 11355
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
SQLblog.com
@SQL_Kiwi
Goldie Lesser
Goldie Lesser
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 1501
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
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10379 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search