How to parse out a column in sql with multiple values

Question

I have a column in a sql server table named [City_St_Zip] that contains records that look like this

Dallas, TX 12345

What I would like to do is separate the column into three different columns (i.e. City, State and Zip) like this:

Dallas

TX

12345

I am not sure how to go about this in SQL

I have tried the following

DECLARE @X NVARCHAR(100),
DECLARE @T NVARCHAR(100),
SELECT
   @X = [City_St_Zip],
   @T = [NewDivision]
FROM
   dbo.Invoice
      CROSS APPLY STRING_SPLIT(@X, ',');

This yielded 0 results so I am pretty sure I did that incorrectly

Any suggestions? I am using SQL Server 2019

EDIT: I also tried this which is closer to what I want

SELECT 
   value
FROM
  dbo.Invoice
   CROSS APPLY STRING_SPLIT([City_St_Zip], ',');

That gives me a result set of:

Dallas

TX 12345

So I guess this is convoluted and needs both a comma and a space delimiter. Would I just put the value through another STRING_SPLIT?

You're passing a comma to STRING_SPLIT as the delimiter, and yet in your example, you seem to want to split by a space. Also, why not pass [City_St_Zip] to the function instead of @X? Also, you should consider how to handle cities that have more than one word in the name if you indeed want to split by a space. — devlin carnate
– devlin carnate, Commented Dec 20, 2019 at 23:17
Your select columns do not include the value output from string_split which is going to be null in this case because you're trying to split @x instead of your City_St_Zip column. See the String_Split docs, https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql — AlwaysLearning
– AlwaysLearning, Commented Dec 20, 2019 at 23:20
@devlincarnate I tried what you suggested please see the updated question — MaxG
– MaxG, Commented Dec 20, 2019 at 23:38
@AlwaysLearning that was an oops on my part thank you for pointing that out I fixed it — MaxG
– MaxG, Commented Dec 20, 2019 at 23:39

Gordon Linoff · Accepted Answer · 2019-12-20 23:43:45Z

1

SQL Server has poor string processing support. And, string_split() is not guaranteed to keep the values in order. And string searches are dangerous -- think New York, New York.

So, a brute force method:

select left(col, charindex(',', col) - 1) as city,
       substring(col, charindex(',', col) + 2, 2) as state,
       right(col, 5) as zipcode

Here is a db<>fiddle.

answered Dec 20, 2019 at 23:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MaxG Over a year ago

Would this apply when getting the original value from a table column. For example dbo.Invoice.[City_St_Zip]? Other than that it works perfectly

Gordon Linoff Over a year ago

@MaxG . . . Yes. The db<>fiddle essentially shows that (the column is named col).

LukStorms · Accepted Answer · 2019-12-21 13:08:03Z

0

If you want to use STRING_SPLIT then this will work without variables.

Sample data:

create table dbo.Invoice
(
  id int identity(1,1) primary key,
  [City_St_Zip] nvarchar(100)
);

insert into dbo.Invoice 
([City_St_Zip]) values
('Dallas, TX 12345'),
('Fort Worth, TX 12345')
GO

2 rows affected

Query:

SELECT inv.*, a.*
FROM dbo.Invoice inv
OUTER APPLY 
(
   SELECT
   [1] AS [City], 
   LTRIM(LEFT([2], 3)) AS [State],
   TRIM(SUBSTRING([2],4,LEN([2]))) AS [Zip]
   FROM
   ( SELECT spl.value
     , ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn
     FROM STRING_SPLIT(inv.[City_St_Zip],',') spl
   ) s
   PIVOT (MAX(value) FOR rn IN ([1],[2])) p
) a;

Result:

id | City_St_Zip          | City       | State | Zip  
-: | :------------------- | :--------- | :---- | :----
 1 | Dallas, TX 12345     | Dallas     | TX    | 12345
 2 | Fort Worth, TX 12345 | Fort Worth | TX    | 12345

db<>fiddle here

Extra:

Using the XML type, this SQL will also work in an earlier version like Sql Server 2012.

SELECT inv.*
, a.City
, RTRIM(LEFT(a.StateZip, CHARINDEX(' ',a.StateZip))) AS State
, LTRIM(SUBSTRING(a.StateZip, CHARINDEX(' ',a.StateZip),LEN(a.StateZip))) AS Zip
FROM dbo.Invoice inv
OUTER APPLY
(
  SELECT X.x AS CityStateZipXml
  , X.x.value('/x[1]','nvarchar(max)') AS City
  , LTRIM(X.x.value('/x[2]','nvarchar(max)')) AS StateZip
  FROM (
    SELECT CAST(CONCAT('<x>', REPLACE(inv.[City_St_Zip],',','</x><x>'),'</x>') AS XML)
  ) AS X(x)
) a;

edited Dec 21, 2019 at 13:08

answered Dec 20, 2019 at 23:40

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

3 Comments

MaxG Over a year ago

This is very close but as mentioned in the comments on my question Fort Worth, TX 12345 comes out like Fort is the City, Worth is the state and TX is the zip code. How would I accommodate for the space in city names

MaxG Over a year ago

Perfect!! Thank you! I had no idea the amount of work just to parse that out.

LukStorms Over a year ago

It used to be worse. There's also alternatives by using the XML type. But I would cheer if MS would someday include something like the MySql SUBSTRING_INDEX.

Novice_Techie · Accepted Answer · 2019-12-20 23:49:26Z

0

* Updated as per SQL Server*

create table ctry
( 
  city_st_zip nvarchar(100)
);

insert into ctry values('Dallas, TX 12345');

--SQL USED--

SELECT
LEFT([city_st_zip], CHARINDEX(',', [city_st_zip]) - 1) AS [City],
SUBSTRING([city_st_zip], CHARINDEX(',', [city_st_zip]) + 2, 2) as [State],
RIGHT([city_st_zip], CHARINDEX(' ', [city_st_zip]) - 2) AS [Zip]
FROM ctry;

--Result--

City    State   Zip
Dallas  TX  12345

edited Dec 20, 2019 at 23:49

answered Dec 20, 2019 at 23:21

Novice_Techie

4443 gold badges12 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to parse out a column in sql with multiple values

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related