SPLIT

Updated 2023-06-15 22:21:31.903000

Syntax

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

Description

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

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

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "d3b7e95a-c582-4f96-ab02-b759897e231f", "colName": "item", "colDatatype": "nvarchar(max)", "colDesc": "the string value"}]}

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.

{"columns":[{"field":"item"}],"rows":[{"item":"The"},{"item":"quick"},{"item":"brown"},{"item":"fox"},{"item":"jumps"},{"item":"over"},{"item":"the"},{"item":"lazy"},{"item":"dog"}]}

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.

{"columns":[{"field":"Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Ticker"},{"field":"Open","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"High","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Low","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Close","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Volume","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Date":"20100729","Ticker":"A","Open":"28.97","High":"29.15","Low":"27.78","Close":"28.15","Volume":"44085"},{"Date":"20100729","Ticker":"AA","Open":"11.1","High":"11.2","Low":"10.87","Close":"11.02","Volume":"144207"},{"Date":"20100729","Ticker":"AAPL","Open":"260.71","High":"262.65","Low":"256.1","Close":"258.11","Volume":"229930"},{"Date":"20100729","Ticker":"ABC","Open":"29.31","High":"29.59","Low":"28.63","Close":"28.98","Volume":"63594"},{"Date":"20100729","Ticker":"ABT","Open":"49.44","High":"49.77","Low":"48.93","Close":"48.98","Volume":"88464"},{"Date":"20100729","Ticker":"ACE","Open":"53.43","High":"53.85","Low":"52.45","Close":"52.97","Volume":"35654"},{"Date":"20100729","Ticker":"ADBE","Open":"28.99","High":"29.1","Low":"28.38","Close":"28.7","Volume":"53202"},{"Date":"20100729","Ticker":"ADI","Open":"30.51","High":"30.59","Low":"29.395","Close":"29.74","Volume":"45727"},{"Date":"20100729","Ticker":"ADM","Open":"27.28","High":"27.45","Low":"26.84","Close":"27","Volume":"117175"},{"Date":"20100729","Ticker":"YHOO","Open":"13.93","High":"13.96","Low":"13.75","Close":"13.76","Volume":"167029"},{"Date":"20100729","Ticker":"YUM","Open":"41.84","High":"42.14","Low":"41.09","Close":"41.33","Volume":"31675"},{"Date":"20100729","Ticker":"ZION","Open":"21.75","High":"22.39","Low":"21.64","Close":"22.05","Volume":"82712"},{"Date":"20100729","Ticker":"ZMH","Open":"53.79","High":"53.79","Low":"51.99","Close":"52.16","Volume":"38369"}]}