SPLIT
Updated: 31 July 2010
Use SPLIT to generate a single column table containing the substrings between the specified delimiter passed to the function.
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 Types
RETURNS TABLE (
[item] [nvarchar](max) NULL
)
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. We are using a file of stock trading activity from http://pages.swcp.com/stocks/. The file looks like this (we have truncated the contents):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
The following SQL will take this string (without the ellipses) and turn it into columnar data with meaningful headings:
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:
