Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Splitting A String Expand / Collapse
Author
Message
Posted Monday, March 15, 2010 4:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:42 AM
Points: 478, Visits: 1,410
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) = '.'


Post #883438
Posted Monday, March 15, 2010 5:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 7,040, Visits: 12,957
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
Post #883465
Posted Monday, March 15, 2010 6:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 27, 2010 2:21 AM
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

Post #883475
Posted Monday, March 15, 2010 8:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #883503
Posted Monday, March 15, 2010 9:08 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 4,006, Visits: 6,069
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? -- Stephen Stills
Post #883506
Posted Monday, March 15, 2010 9:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 4,006, Visits: 6,069
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? -- Stephen Stills
Post #883507
Posted Tuesday, March 16, 2010 12:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #883570
Posted Tuesday, March 16, 2010 7:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #883738
Posted Tuesday, March 16, 2010 10:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:42 AM
Points: 478, Visits: 1,410
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.
Post #883965
Posted Tuesday, March 16, 2010 12:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 7,040, Visits: 12,957
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
Post #884080
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse