How To Split A String in SQL

Anyone who has worked with SQL long enough comes to a point where they need to split strings into multiple fields. For simple strings how would you do this? Lets take a look at some simple examples.

Example 1

Take this example:

DECLARE @String AS VARCHAR(100) = ‘2020-12-15,$15.00’

SELECT CHARINDEX(‘,’,@String)

–SPLIT INTO TWO COLUMNS
SELECT LEFT( @String, CHARINDEX(‘,’,@String)-1) AS [Transaction_DTTM]
,RIGHT( @String, LEN(@String) – CHARINDEX(‘,’,@String)) AS [Transaction_Amount]

We have a string value in the @String variable that contains both a date and dollar amount and is separated by a comma ( ‘,’ ). We need to separate the date value into one column named Transaction_DTTM and the amount into another column named Transaction_Amount.
SQL has many functions that can be used to interact with strings. You will often need to use different functions to get the results that you need.

We have a string value in the @String variable that contains both a date and dollar amount and is separated by a comma ( ‘,’ ). We need to separate the date into one column named Transaction_DTTM and the amount into another column named Transaction_Amount.

SQL has many functions that can be used to interact with strings. You will often need to use different functions to get the results that you need.

In this example I use:

LEN – Get the total number of characters in the string.
CHARINDEX – We use this to find the location of the comma.
LEFT – Get all characters to the left of the location returned by CHARINDEX.
RIGHT – Get all the characters to the RIGHT of the comma. This is calculate by LEN of the string – commas location.

Example 2

But what if you need get more than two columns of data from a string. Here is an example using where we need to use three values. Adding just one more value that needs to be split from the string makes the SQL more complex.

Here we need to retrieve the transaction owner ( Bob ) as well as the date and dollar.

–Rewrite it using Substring
DECLARE @String AS VARCHAR(100) = ‘Bob,2020-12-15,$15.00’

SELECT [Transaction_Owner]
      ,LEFT( [RightPart], CHARINDEX(‘,’,[RightPart])-1)               AS [Transaction_DTTM]
      ,RIGHT( [RightPart], LEN([RightPart]) – CHARINDEX(‘,’,[RightPart])) AS [Transaction_Amount]

FROM (

    SELECT LEFT( @String, CHARINDEX(‘,’,@String)-1)               AS [Transaction_Owner]
          ,RIGHT( @String, LEN(@String) – CHARINDEX(‘,’,@String)) AS [RightPart]

) AS [FinancialData]

As you can see the query requires a lot more SQL than in example 1. The above first gets the transaction owner, then pushes that result through a derived table [FinancialData]. It then retrieves the transaction date and amount from the RightPart column.

Another approach would be to use the SUBSTRING function.

DECLARE @Str AS VARCHAR(100) = ‘Bob,2020-12-15,$15.00’

–SPLIT INTO TWO COLUMNS
SELECT SUBSTRING ( @Str, 1, CHARINDEX(‘,’,@Str) -1 )              AS [Transaction_DTTM]
      ,SUBSTRING ( @Str,
                      CHARINDEX(‘,’,@Str) +1,
                              CHARINDEX(‘,’,@Str, CHARINDEX(‘,’,@Str) + 1 ) – CHARINDEX(‘,’,@Str)-1 )
      ,SUBSTRING ( @Str, CHARINDEX(‘,’,@Str, CHARINDEX(‘,’,@Str) + 1 )+1, LEN(@Str) )

SUBSTRING – Takes a string as an input, the starting location in the string, and how many characters to return from the starting location in the string.

The above example works if you need three fields, but what if you need 4 , or 5, or 6, etc. As you can imagine, the SQL can get complex and would require a lot of nested LEFT, RIGHT, and SUBSTRING functions.

Example 3

When the number of values that need to be split out of a string increases, you will likely need to change you approach. In this example we have mock records of some financial transactions. The records are stored in a CSV file and need to be split into their own columns.

The values we want are:

TransactionDate
PostDate
Location
Memo
TransactionType
Amount

DECLARE @FT AS TABLE
(
    ID INT IDENTITY(1,1)
   ,Purchase VARCHAR(250)
)

INSERT INTO @FT ( Purchase )
VALUES (’12/14/2020,12/15/2020,ONLINE-RETAILER.ORG,Purchase Store Item,Sale,-99.99′ )
      ,(’12/15/2020,12/15/2020,GAS STATION,Gas,Sale,-25.00′ )
      ,(’12/17/2020,12/18/2020,OTHER-RETAILER.ORG,Purchase,Sale,-125.00′ )
      ,(’12/20/2020,12/21/2020,ONLINE-RETAILER.ORG,Purchase Store Item,Sale,-275.00′ )
      ,(’12/20/2020,12/22/2020,GROCERY STORE,Food,Sale,-150.00′ )
      ,(’12/17/2020,12/18/2020,ONLINE-RETAILER.ORG,Gifts & Donations,Sale,-500.00′ )

;WITH CTE As (

SELECT Id, ROW_NUMBER() OVER( PARTITION BY [ID] ORDER BY [N] ) AS [ColId], [Value]
FROM (
SELECT NULL AS [N], ID, [Value]
FROM @FT
CROSS APPLY STRING_SPLIT(Purchase,’,’)
) AS [S]

)

SELECT [1] AS [TransactionDate]
      ,[2] AS [PostDate]
      ,[3] AS [Location]
      ,[4] AS [Memo]
      ,[5] AS [TransactionType]
      ,[6] AS [Amount]
FROM (
SELECT Id, ColId, [Value] FROM CTE
) AS [P]
PIVOT
(
    MAX([VALUE]) FOR ColId IN ([1],[2],[3],[4],[5],[6])
) AS [Q]

In this example we use the STRING_SPLIT function and PIVOT to do the majority of the work getting the values we need. I’ll cover those two functions in a different post. Just know that the STRING_SPLIT function is taking the string from the purchase column, splitting the string by the comma character, and then placing each instance in a row. Then the PIVOT function it rotates those rows and moves them into their own column.