Split Delimited String into Columns in SQL Server

Problem

Typically, in SQL Server data tables and views, values such as a person’s name or their address is stored in either a concatenated string or as individual columns for each part of the whole value. For example: John Smith 123 Happy St Labor Town, CA.

Data storage options include:

  • Single column with all the data
  • Two columns separating the person’s name from their address
  • Multiple columns with a column for each piece of the total value of the data

Solution

With this, DBA’s are inevitably always having to concatenate or parse the values to suit our customer’s needs.

To build on our sample from above, I have an address column that has the full address (street number and name, city and state) separated by commas in a concatenated column. I want to break that data into three columns (street, city and state) respectively.

Key Takeaways

  • In SQL Server, data often combines values like names and addresses in single strings or multiple columns.
  • DBAs frequently need to parse or concatenate these values to meet customer needs, especially for addresses stored as a single string.
  • The article demonstrates how to split a concatenated address into separate columns using SQL.
  • It introduces the PARSENAME function, which helps break down delimited data into individual string segments for easier query handling.
  • The solution includes examples that show both simple and complex scenarios for parsing strings to improve data organization.

Setup Sample Environment

For this tip, let’s create a test database and test table for the parsing example. In this scenario, we have a street name and number, followed by the city, then the state.

USE master;
GO
CREATE DATABASE myTestDB;
GO
USE myTestDB;
GO

The following code creates a table in the new (test) database with only two columns – an id column and an address column.

CREATE TABLE dbo.custAddress(
     colID INT IDENTITY PRIMARY KEY
   , myAddress VARCHAR(200)
   );
GO

Populate the table with some generic data. Notice that the address is one string value in one column.

INSERT INTO dbo.custAddress(myAddress)
VALUES('7890 - 20th Ave E Apt 2A, Seattle, VA')
    , ('9012 W Capital Way, Tacoma, CA')
    , ('5678 Old Redmond Rd, Fletcher, OK')
    , ('3456 Coventry House Miner Rd, Richmond, TX')
GO

Confirm the table creation and data entry with the following SQL SELECT statement.

SELECT *
FROM dbo.custAddress;
GO

Your results should return, as expected, two columns – the id column and the address column. Notice the address column is returned as a single string value in image 1.

query results

SQL Split String into Columns

Breaking down the data into individual columns. The next step will be to parse out the three individual parts of the address that are separated by a comma.

SELECT 
     REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 1)) AS [Street]
   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 2)) AS [City]
   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 3)) AS [State]
FROM dbo.custAddress;
GO

Since we didn’t call the id column in the SELECT statement above, the results only returned the address string divided into three columns, each column returning a portion of the address for the respective column as shown in image 2.

query results

Real World Example for SQL Split Column by Delimiter

Here in the real world, DBA’s are often faced with more complex tables or views and not just a simple two column table as in the above sample. Although the sample above is a great primer for dissecting how to parse a string value, the following section demonstrates a more complex situation.

Again, create a sample table in our test database to work with. Here, we create a slightly more complex table with some additional columns.

CREATE TABLE employeeData(
     colID INT IDENTITY PRIMARY KEY
   , empID INT
   , empName VARCHAR(50)
   , empAddress VARCHAR(200)
   , empPhone VARCHAR(12)
   , jobClass VARCHAR(50)
   );
GO

Insert some generic data into the test table.

INSERT INTO employeeData(empID, empName, empAddress, empPhone, jobClass)
VALUES (1, 'John, M, Smith, Jr', '123 Happy Hollow, BarnYard, OK, 90294', '202-555-0118', 'Programmer')
     , (2, 'Joe, S, Jones, Sr', '456 Sad Ln, BarnDoor, TX, 90295', '202-555-0195', 'Tester')
     , (3, 'Sammy, L, Smuthers', '5655 Medow Lane, Pastuer, CA, 90296', '202-555-0192', 'Sales') 
     , (4, 'Henry, R, Lakes, Esq', '8749 Sunshine Park, Glenndale, HA, 90297', '202-555-0141', 'Manager')
     , (5, 'Harry, Q, Public, Jr', '555 Somber Ln, Levy, OR, 90298', '202-555-0137', 'Graphical Designer')
GO

Now, let’s create a mixed SELECT statement to pull all the columns that breaks down the “empName”, “empAddress” and “empPhone” columns into multiple columns.

In the following block of code, notice that I restarted my “place / position” count on each column that I want to parse. Each time you start parsing a new column, you must start your count over. You should also note that the “empName” and “empAddress” string values are separated with a comma while the “empPhone” string value is separated by a hyphen and thus should be reflected in your “parse” function between the first set of single quotes.

SELECT 
   colID
   , empID
   -- The following section breaks down the "empName" column into three columns.
   , REVERSE(PARSENAME(REPLACE(REVERSE(empName), ',', '.'), 1)) AS FirstName
   , REVERSE(PARSENAME(REPLACE(REVERSE(empName), ',', '.'), 2)) AS MiddleName
   , REVERSE(PARSENAME(REPLACE(REVERSE(empName), ',', '.'), 3)) AS LastName
   -- The following section breaks down the "empAddress" column into four columns.
   , REVERSE(PARSENAME(REPLACE(REVERSE(empAddress), ',', '.'), 1)) AS Street
   , REVERSE(PARSENAME(REPLACE(REVERSE(empAddress), ',', '.'), 2)) AS City
   , REVERSE(PARSENAME(REPLACE(REVERSE(empAddress), ',', '.'), 3)) AS State
   , REVERSE(PARSENAME(REPLACE(REVERSE(empAddress), ',', '.'), 4)) AS ZipCode
   -- The following section breaks down the "empPhone" column into three columns
   , REVERSE(PARSENAME(REPLACE(REVERSE(empPhone), '-', '.'), 1)) AS AreaCode
   , REVERSE(PARSENAME(REPLACE(REVERSE(empPhone), '-', '.'), 2)) AS Prefix
   , REVERSE(PARSENAME(REPLACE(REVERSE(empPhone), '-', '.'), 3)) AS LastFour
   , jobClass
FROM employeeData;
GO

The results should return thirteen columns from the six columns queried against as shown in image 3.

query results

Summary

In summary, the PARSENAME function is a handy addition to your T-SQL toolkit for writing queries involving delimited data. It allows for parsing out and returning individual segments of a string value into separate columns. Since the PARSENAME function breaks down the string, you are not obligated to return all the delimited values. As in our sample above, you could have returned only the area code from the “empPhone” column to filter certain area codes in your search.

Next Steps

Related Posts

14 Comments

  2. Hi John,

    see if this works for your needs: https://www.mssqltips.com/sqlservertip/6390/sql-server-split-string-replacement-code-with-stringsplit/

    -Greg

  3. This is great, except I can’t get it to work if there are 4-5 periods in the text string. I have a text string that can contain 0-5 periods, separating the string into up to 6 parts. Your solution works great if there are 0-3 periods (1-4 parts), but if there are 4-5 periods (5-6 parts) I get returned for ALL 6 parts of those strings. I can’t figure out why. It’s not-related to string length, as some of the 3 or 4-part strings are longer than some of the 5 or 6-part strings.

    • The PARSENAME() function is not intended to be a generic “string-split” function. It is intended to return one part of a multi-part SQL object name. SQL objects can be referred to with up to 4-part names (database, schema, major & minor). For example a 4-part name for a table contained within a schema in a database on a server might look like “MyServer.MyDB.MySchema.MyTable”. Depending on the current context this column could be referred to with a 3-part or name or less (e.g. if the current context is already within the MyDB database on MyServer you can refer to it simply as “MySchema.MyTable”.
      This is why PARSENAME() cannot be used when there are more than 4 terms separated. It is also why the separator that YOU are using for your string needs to be replaced with a period “.” before calling PARSENAME() – this tip is using a function for a purpose it was not intended.

      The REVERSE() function is used in the above example (twice for each term) because the 2nd parameter “object_piece” works from right-to left. e.g. object_piece 1 is the table name & 4 is the Server name. This allows ParseName() to correctly return the correct part of the name present (i.e. table, schema, database, server) for 4-part, 3-part, 2-part & even 1-part names.
      Depending on how your delimited data is structured, you may be able to simplify the expressions in this tip to extract a term from a delimited string without using REVERSE. i.e. these expressions:
      , REVERSE(PARSENAME(REPLACE(REVERSE(empName), ‘,’, ‘.’), 1)) AS FirstName
      , REVERSE(PARSENAME(REPLACE(REVERSE(empName), ‘,’, ‘.’), 2)) AS MiddleName
      , REVERSE(PARSENAME(REPLACE(REVERSE(empName), ‘,’, ‘.’), 3)) AS LastName

      could become (note the reversed order of the “object_piece” parameter:
      , PARSENAME(REPLACE(empName, ‘,’, ‘.’), 3) AS FirstName
      , PARSENAME(REPLACE(empName, ‘,’, ‘.’), 2) AS MiddleName
      , PARSENAME(REPLACE(empName, ‘,’, ‘.’), 1) AS LastName
      So long as the input string always contains 3 parts this simpler expression will work the same.
      PARSENAME() has limitations when used to parse out a delimited string, most notably the 4-part limit as it’s intended purpose is to parse a 4-part database object name.

      If you have more than 4-parts in a delimited string, you may want to consider leveraging the TVF (example): SELECT * FROM string_split(‘123 Happy Hollow, BarnYard, OK, 90294, USA’, ‘,’, 1) to return a 2-column table, with each row containing the ordinal position or each term and the delimited term (the above example returns 5 rows. You can then incorporate this table into your query / SELECT clause to display the separated terms:

      SELECT (SELECT [value] FROM String_Split(empAddress, ‘,’, 1) WHERE ordinal=1) as Street,
      (SELECT [value] FROM String_Split(empAddress, ‘,’, 2) WHERE ordinal=1) as City,
      …
      (SELECT [value] FROM String_Split(empAddress, ‘,’, 5) WHERE ordinal=1) as Country,
      …

  4. Great article! For me this method does not work because I have decimal values in the string to be split up, so I will be splitting the whole number from the decimal part. I solved the challenge with a loop that I implemented on a trigger. The trigger is attached to a drop table and fires on insert. The target value is split and parsed into the respective columns in a curated table. Here is my code: https://github.com/hristochr/SQL-Playground/blob/master/split_by_delimiter_with_trigger.sql

  5. Warren,

    You are very welcome.
    I love it when someone benefits from my articles. That’s what they are here for.
    Thanks for posting a comment.

  7. Nice article, Aubrey. There are a couple of big pieces missing, though.

    One of the pieces with two possible serious problems is that the return is of the SYSNAME datatype. That means two things:
    1. The elements within a string must be less that 128 characters. That’s usually not a problem but if someone is expecting to be able to split out longer elements, it’s not going to work.
    2. The results are the equivalent of NVARCHAR(128) and if you use any of those values as criteria during a lookup, you may run into serious datatype mismatch issues that result in full table scans.

    As a sidebar, names and addresses frequently contain periods and those will need to be replaced by another character first and maybe put back later.

  8. The only issue with using this method is when you have a string with more than 4 values in it. For example “Name, Address, Phone, Email, Income”. This method would not work, as PARSENAME method can only be used till 4 levels. A generic CharIndex would be a more sustainable option.

  9. Thanks for the comment Alex, I’m glad it worked for you.
    I try to make all my code examples work out-of-the-box with just a simple copy/paste.

  10. Works fine just by C&P. I still learning, but had the question to separate a column containing numbers as text (07.2021) for month and year into separate colums one for month, one for year. Exactly what i needed – simple and useful.

  11. It might lessen some confusion if image 1 and the preceding data insert statements agree. Your insertion of data has no ‘.’ in the data, while the image clearly shows ‘.’ in the data.

  12. Would this be an opportunity to use CROSS APPLY to perform the reverse & replace on the column(s) once so they can then be parsed and reversed? It seems like a solution like this would be less CPU intensive.

    SELECT
    REVERSE(PARSENAME(ar.AddressRev, 1)) AS [Street]
    , REVERSE(PARSENAME(ar.AddressRev, 2)) AS [City]
    , REVERSE(PARSENAME(ar.AddressRev, 3)) AS [State]
    FROM dbo.custAddress ca
    cross apply ( select REPLACE(REVERSE(myAddress), ‘,’, ‘.’) as AddressRev ) ar;

  13. hello. i need a help. i tried the code and it is working well, but i need to insert the output into a another table. how to do this??if anyone help me. please help

Leave a Reply

Your email address will not be published. Required fields are marked *