CODEWITHSUNDEEP
sqlsql-server
MaxG
MaxG

Reputation: 19

How to parse out a column in sql with multiple values

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?

Upvotes: 0

Views: 1753

Answers (3)

LukStorms
LukStorms

Reputation: 29647

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;

Upvotes: 0

Novice_Techie
Novice_Techie

Reputation: 444

* 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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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.

Upvotes: 1

Related Questions