SQL Server SPLIT Function

Updated 2023-06-15 22:21:31.903000

Description

Use the table-valued function SPLIT to split a delimited string and return the split values in order .

Syntax

SELECT * FROM [wctString].[wct].[SPLIT] (
  <@SourceString, nvarchar(max),>
 ,<@Delimiter, nvarchar(4000),>)

Arguments

@SourceString

the string to be split. @SourceString must be of the type nvarchar or of a type that implicitly converts to nvarchar.

@Delimiter

the string characters used to identify substring limits. @Delimiter must be of the type nvarchar or of a type that implicitly converts to nvarchar.

Return Type

table

Remarks

Consecutive delimiters will return a blank row

Examples

Splitting a space delimited string.

SELECT *
FROM wct.SPLIT('The quick brown fox jumps over the lazy dog',' ');

Here is the resultant table.

Here's an example where we combine the SPLIT function and the PARSE function on a string where records are separated by a carriage return/line feed combination and columns, within a record, are separated by commas.

select wct.PARSE(item,',',1) as [Date]
,wct.PARSE(item,',',2) as [Ticker]
,wct.PARSE(item,',',3) as [Open]
,wct.PARSE(item,',',4) as [High]
,wct.PARSE(item,',',5) as [Low]
,wct.PARSE(item,',',6) as [Close]
,wct.PARSE(item,',',7) as [Volume]
from wct.SPLIT('20100729,A,28.97,29.15,27.78,28.15,44085
20100729,AA,11.1,11.2,10.87,11.02,144207
20100729,AAPL,260.71,262.65,256.1,258.11,229930
20100729,ABC,29.31,29.59,28.63,28.98,63594
20100729,ABT,49.44,49.77,48.93,48.98,88464
20100729,ACE,53.43,53.85,52.45,52.97,35654
20100729,ADBE,28.99,29.1,28.38,28.7,53202
20100729,ADI,30.51,30.59,29.395,29.74,45727
20100729,ADM,27.28,27.45,26.84,27,117175
20100729,YHOO,13.93,13.96,13.75,13.76,167029
20100729,YUM,41.84,42.14,41.09,41.33,31675
20100729,ZION,21.75,22.39,21.64,22.05,82712
20100729,ZMH,53.79,53.79,51.99,52.16,38369',wct.CRLF());

Which returns the following result.