Split Delimited String into Columns in SQL Server with PARSENAME

By:   |   Updated: 2020-02-19   |   Comments (11)   |   Related: More > TSQL

Master Your Data Environment With DataOps

Free MSSQLTips Webinar: Master Your Data Environment With DataOps

Learn tips and tricks on how to master your data environment with SolarWinds® DataOps solutions, which are designed to help you streamline essential database tasks, database projects, and data-centric application development. Let us show you how to save time on tedious but critical database documentation tasks.


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. This data could be stored in a single column with all the data, in two columns separating the person's name from their address or in multiple columns with a column for each piece of the total value of the data.

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.


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;


USE myTestDB;

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(
   , myAddress VARCHAR(200)

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')

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

FROM dbo.custAddress;

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

Image 1

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.

     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;

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

Image 2

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(
   , empID INT
   , empName VARCHAR(50)
   , empAddress VARCHAR(200)
   , empPhone VARCHAR(12)
   , jobClass VARCHAR(50)

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')

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.

   , 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;

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

query results

Image 3

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

get scripts

next tip button

About the author
MSSQLTips author Aubrey Love Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips

Article Last Updated: 2020-02-19

Comments For This Article

Wednesday, July 7, 2021 - 2:47:15 PM - Aubrey Back To Top (88962)
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.

Wednesday, July 7, 2021 - 7:14:07 AM - Alex Back To Top (88956)
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.

Thursday, March 18, 2021 - 11:43:25 AM - Mike Back To Top (88437)
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.

Thursday, March 18, 2021 - 10:07:20 AM - Paul Hunter Back To Top (88436)
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.

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;

Monday, January 25, 2021 - 5:12:02 AM - Babita Back To Top (88096)
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

Monday, October 5, 2020 - 5:16:14 PM - saminda Back To Top (86601)
wow this is really really great, I spent almost one day coding this thing by myself using charindex e.t.c
this is great, thank you, and wish you very best, you are a great DBA.

Thursday, October 1, 2020 - 1:02:10 PM - Adam Walker Back To Top (86580)
Really helpful. But I have a slightly more challenging item that I'm struggling with. I have a single columns that can hold an Email (prefixed by EM), a Mobile phone (prefixed by MO), Fax Phone No. (prefixed by FX), phone number 1 (prefixed by P1) and phone number 2 (prefixed by P2) all of which can appear in different orders and may contain trailing spaces. I've also noticed in some cases these fields are present on more than 1 occasion. Phone numbers are typically displayed as 32 characters and the total field length is a max of 224 characters. Any suggestions on reliable passing these as seperate columns as I need to perform data quality checks on these.

Tuesday, February 25, 2020 - 11:22:01 AM - Ben Back To Top (84790)

Carefull! There are many caveats to using this.  There is a limitation on length of string, how many delimiters you can have, your text cannot have any periods in it are some of the ones I have found.  Reverse is used because results are ordered/returned from right to left. Any errors result in a null return.

Monday, February 24, 2020 - 8:54:47 AM - Gerard Back To Top (84762)

Great tip! It would be good to mention, that PARSENAME function is designed for the specific purpose - to parse names of database object names - and it is limited to four parts in these names, limited by dot: "Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name.". Without a mention like this the tip suggests, that PARSENAME function is a general purpose, which is not true.

Wednesday, February 19, 2020 - 3:21:15 PM - Aubrey Back To Top (84644)


Thank you for your reply. Yes, there a dozen different ways of parsing out the data of a string, this is just one of them. The REVERESE simply allows for a way of ordering the comma delimited string in your results. This sample was just a simple example that works across multiple versions of SQL Server Management Studio uniformly.

Wednesday, February 19, 2020 - 8:00:13 AM - Ozan Dikerler Back To Top (84635)

Actually, you don't need to REVERSE 2 times. I removed all reverse functions and it still works good. Why do you need reverse?


get free sql tips
agree to terms