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

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Split String Function

By Chris Kutsch,

This In-line Table Valued Function takes a string of values along with a delimiter and breaks the string into a table using recursion. Support has been added for text qualified strings as well as space and tab delimited records.

Using a numbers table, we are able to take advantage of Recursion in SQL Server to find our delimiter and leverage the SUBSTRING function to break the string into its individual components without requiring the overhead of a loop.

Once the function has been created, splitting strings is quite simple.

DECLARE @String NVARCHAR(MAX)
    ,   @Delimiter NVARCHAR(3);
    
SELECT  @String = '"The","Quick","Brown","Fox","Jumped","Over","The","Lazy","Dog","0123456789","`~!@#$%^&*()_-+={}[]|\:;<,>.?/"'
    ,   @Delimiter = '","';

SELECT  s.Record
    ,   s.Value
    ,   @String AS [@String]
FROM    dbo.udf_SplitString(@String,@Delimiter) AS s;

This script will produce the following results.

Happy Scripting!

Total article views: 3916 | Views in the last 30 days: 6
 
Related Articles
SCRIPT

Delimited String Parsing Functions - Big Set

Feed it large strings of delimited horizontal data and it returns it back as a vertical table.

SCRIPT

Delimited String Parser

Parses delimited string into a table of up to 9 varchar fields.

FORUM

Extracting Rows from Delimited Strings

Extracting Rows from Delimited Strings

SCRIPT

Delimited String Parsing Functions - Big2D set

Feed it large strings of double-delimited horizontal data and it returns it back as a non-pivoted ve...

SCRIPT

Get String between two delimiters

Get string between two delimiters

Tags
parse string    
recursion    
substring    
 
Contribute