Click here to Skip to main content
15,180,782 members
Articles / General Programming / Algorithms
Alternative
Tip/Trick
Posted 11 Sep 2015

Tagged as

Stats

11.3K views
2 bookmarked

Split Any Delimited String in SQL

Rate me:
Please Sign up or sign in to vote.
4.83/5 (3 votes)
11 Sep 2015CPOL
This is an alternative for "Split Any Delimited String in SQL"
It's still not a very good solution ("any" is most inaccurate), but here's a Recursive Common Table Expression way to do it.
SQL
CREATE FUNCTION dbo.fnSimpleSplit 
( @InputString NVARCHAR(MAX)
, @Delimiter   NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
  -- SELECT * FROM dbo.fnSimpleSplit ( 'Apple, Mango, Orange, Pineapple' , ',' )

  WITH cte AS
  (
    SELECT CAST(NULL AS NVARCHAR(MAX)) val , @InputString + @Delimiter s , CHARINDEX(@Delimiter,@InputString) offset
  UNION ALL
    SELECT SUBSTRING(s,1,offset-1) , SUBSTRING(s,offset+1,LEN(s)) , CHARINDEX(@Delimiter,s,offset+1)-offset
    FROM cte
    WHERE offset>0
  )
  SELECT val
  FROM cte
  WHERE val IS NOT NULL
)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

PIEBALDconsult
Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian

---------------

"I would be looking for better tekkies, too. Yours are broken." -- Paul Pedant

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming

Comments and Discussions

 
QuestionA Possible Alternative Pin
David A. Gray14-Sep-15 10:10
MemberDavid A. Gray14-Sep-15 10:10 
It seems to me that string splitting is the sort of activity that begs for implementation as an extended stored procedure. A great starting point for one would be my AnyCSV class library, described and published, with source code, in A Robust CSV Reader.

Rather than tackle a whole file full of delimited strings, this class confines itself to robust processing of single strings. The name, AnyCSV, is a tad misleading, because it handles any delimited string, although the defaults are geared towards standard CSV strings. Limiting it to processing one string makes this class ideal for use in situations that require processing a one-off string, as you might encounter in a stored procedure. On the other hand, it can just as easily be embedded in a loop or using block to process the records read from a stream.

Moreover, you have a choice of using static methods that include overrides that permit all of the operating parameters that govern its behavior to be overridden, or an instance that stores values for all of the parameters that should remain unchanged throughout the processing of a set of strings, such as the contents of a file. Storing the parameters in an instance makes calling the instance Parse method trivial; the only parameter is the string to be processed.

Regardless of how you use it, the parser returns an array of System.String objects, easily marshaled into native SQL Server strings. If I had a need for such a thing, the ESP would already exist. Wink | ;)
AnswerRe: A Possible Alternative Pin
PIEBALDconsult14-Sep-15 18:37
professionalPIEBALDconsult14-Sep-15 18:37 
GeneralRe: A Possible Alternative Pin
David A. Gray15-Sep-15 13:14
MemberDavid A. Gray15-Sep-15 13:14 
GeneralRe: A Possible Alternative Pin
PIEBALDconsult15-Sep-15 13:29
professionalPIEBALDconsult15-Sep-15 13:29 
GeneralRe: A Possible Alternative Pin
David A. Gray15-Sep-15 13:55
MemberDavid A. Gray15-Sep-15 13:55 
GeneralRe: A Possible Alternative Pin
Jovan Popovic(MSFT)16-Sep-15 5:23
MemberJovan Popovic(MSFT)16-Sep-15 5:23 
GeneralRe: A Possible Alternative Pin
PIEBALDconsult16-Sep-15 6:20
professionalPIEBALDconsult16-Sep-15 6:20 
GeneralRe: A Possible Alternative Pin
David A. Gray16-Sep-15 9:40
MemberDavid A. Gray16-Sep-15 9:40 
GeneralRe: A Possible Alternative Pin
PIEBALDconsult16-Sep-15 12:45
professionalPIEBALDconsult16-Sep-15 12:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.