Split string using multiple delimiters

Question

Post reply

Split string using multiple delimiters

ece.viky

Right there with Babe

Points: 727
More actions
October 20, 2014 at 8:52 am

#311952

Comments posted to this topic are about the item Split string using multiple delimiters

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Interesting - does this work out faster than using the REPLACE function?

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

FROM N3 AS X, N3 AS Y)

SELECT @derived_string=STUFF((SELECT '' + (Case When

PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0

Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)

FROM N4 Nums WHERE Nums.n<=LEN(@String) FOR XML PATH('')),1,0,'')

The section of code after this in your function is a simple XML string splitter. This article [/url]discusses the relative merits and otherwise of several types of string splitter and exposes the two main issues with XML string splitting - poor performance and sensitivity to XML control characters.

Other issues are:

OVER(ORDER BY X.n) will introduce a sort into the plan, OVER(ORDER BY (SELECT NULL)) won't.

There's no ORDER BY in the FOR XML PATH concatenator - I don't think you can guarantee the order of the elements in the reassembled string.

^{“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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

May 18, 2016 at 6:32 am

#1879814

Thanks for the script.

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

Iwas Bornready (5/18/2016)
Thanks for the script.

Reported as spam

^{“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

Protiguous SSC Eights! Points: 915 More actions · Answer 4

ChrisM@Work (5/18/2016)
Iwas Bornready (5/18/2016)
Thanks for the script.
Reported as spam

Spam?

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

Rick Harker (5/18/2016)
ChrisM@Work (5/18/2016)
Iwas Bornready (5/18/2016)
Thanks for the script.
Reported as spam
Spam?

Yes, according to several definitions of spam found on Google. Here's one good contender: "•multiple copies of an identical or nearly-identical message sent to a large number of...".

These utterly pointless posts by "Iwas Bornready" needlessly bring old threads to the top of the current topics list. There may or may not even be a script in the thread.

Say you're at your wit's end trying to fathom out a production issue which is preventing your users from connecting, or you've got a severe deadlocking problem. You post it up on ssc confident that you will have intelligent and constructive discussion within minutes, only to find that your emergency post has been pushed off the bottom of the list by multiple threads containing only "Thanks for the script".

^{“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

Marcia J SSCertifiable Points: 5649 More actions · Answer 6

I would be interested in hearing the moderator's opinion on this. I wouldn't have thought that a thank you message would be considered spam.