CODEWITHSUNDEEP
sql-servert-sqlsubstring
NewCoder04
NewCoder04

Reputation: 19

Getting 5 digit Zip Code from string Data

I have a string that contains City,State Zip, and Country. I have been able to get the City and State from the string into a separate column but I ran into issues getting the zip code pulled out. Here is an example of the data I am pulling from.

Columbus,OH 43219, United States

The code I have tried is this 

Replace(LTRIM(RTRIM(LEFT(C.[BILLING_CITYSTATEZIP], CHARINDEX(',', C.[BILLING_CITYSTATEZIP])))),',','')as City,

   left(LTRIM(RTRIM(SUBSTRING(C.[BILLING_CITYSTATEZIP], CHARINDEX(',', C.[BILLING_CITYSTATEZIP])+1, LEN(C.[BILLING_CITYSTATEZIP])-(CHARINDEX('', C.[BILLING_CITYSTATEZIP])-1)))),2)as State,

   dbo.udf_GetNumeric(Right(LTRIM(RTRIM(Substring(C.[BILLING_CITYSTATEZIP], CHARINDEX(',', C.[BILLING_CITYSTATEZIP])+1, LEN(C.[BILLING_CITYSTATEZIP])-(CHARINDEX('',C.[BILLING_CITYSTATEZIP]-1)))),2))as Zip,

I included the code I used to pull the City and State because I mirrored the line for the Zip code off of the State line but I keep getting the error below. I can't figure out where I am missing the argument for the ltrim. Any advice would be welcome.

Msg 174, Level 15, State 1, Line 36 The ltrim function requires 1 argument(s). Msg 102, Level 15, State 1, Line 52 Incorrect syntax near ','.

Upvotes: 0

Views: 2242

Answers (4)

Alan Burstein
Alan Burstein

Reputation: 7918

When dealing with complex string expressions APPLY is your best friend. You can use it to make your code much DRYer (Don't Repeat Yourself). This makes code much cleaner and easier to debug. 

DECLARE @BILLING_CITYSTATEZIP VARCHAR(200) = ' Columbus ,OH 43219, United States ';

SELECT
  City    = SUBSTRING(f.String, a.Head,  a.Pos-1),
  [State] = SUBSTRING(f.String, a.Pos+1, b.Pos-a.Pos-1), 
  Zip     = SUBSTRING(f.String, b.Pos+1, c.Pos-b.Pos-1),
  Country = SUBSTRING(f.String, c.Pos+1, 200) -- BONUS
FROM        (VALUES(RTRIM(LTRIM(REPLACE(REPLACE( -- All trimming (outer & inner) in 1 spot
             @BILLING_CITYSTATEZIP,', ',','),' ,',','))))) AS f(String)
CROSS APPLY (VALUES(CHARINDEX(',',f.String),1))            AS a(Pos,Head)
CROSS APPLY (VALUES(CHARINDEX(' ',f.String,a.Pos+1)))      AS b(Pos)
CROSS APPLY (VALUES(CHARINDEX(',',f.String,b.Pos+1)))      AS c(Pos);

Returns:

City        State    Zip     Country
----------- -------- ------- -----------------
Columbus    OH       43219   United States

Against a table it looks like this:

DECLARE @table TABLE (SomeId INT IDENTITY, BILLING_CITYSTATEZIP VARCHAR(200));
INSERT  @table VALUES ('Columbus,OH 43219, United States'),('Chicago ,IL 60625, United States')

SELECT t.SomeId, f.City, f.[State], f.[Zip], f.Country 
FROM   @table AS t
CROSS APPLY
(
  SELECT
    City    = SUBSTRING(f.String, a.Head,  a.Pos-1),
    [State] = SUBSTRING(f.String, a.Pos+1, b.Pos-a.Pos-1), 
    Zip     = SUBSTRING(f.String, b.Pos+1, c.Pos-b.Pos-1),
    Country = SUBSTRING(f.String, c.Pos+1, 200)
  FROM        (VALUES(RTRIM(LTRIM(REPLACE(REPLACE(
               t.BILLING_CITYSTATEZIP,', ',','),' ,',','))))) AS f(String)
  CROSS APPLY (VALUES(CHARINDEX(',',f.String),1))            AS a(Pos,Head)
  CROSS APPLY (VALUES(CHARINDEX(' ',f.String,a.Pos+1)))      AS b(Pos)
  CROSS APPLY (VALUES(CHARINDEX(',',f.String,b.Pos+1)))      AS c(Pos)
) AS f;

Returns:

SomeId   City       State  Zip    Country
-------- ---------- ------ ------ ----------------
1        Columbus   OH     43219  United States
2        Chicago    IL     60625  United States

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40319

For "City", buried in there you have

- (CHARINDEX('', C.[BILLING_CITYSTATEZIP])-1)

For "Zip", in the same spot you have

 - (CHARINDEX('', C.[BILLING_CITYSTATEZIP]-1))

Note that the closing parenthesese are different. Looks like you need to move one of those two to "before" that -1.

Upvotes: 0

Andrey Belykh
Andrey Belykh

Reputation: 2654

You can use something like this:

select case when patindex('% [0-9][0-9][0-9][0-9][0-9],%', c) > 0 then
SUBSTRING(c, patindex('% [0-9][0-9][0-9][0-9][0-9],%', c) + 1, 5)
else '' end as zipCode
from
(
select 'Columbus,OH 43219, United States' as c
union all
select 'Chicago,IL 60626, United States' as c
) c

Upvotes: 3

Icculus018
Icculus018

Reputation: 1066

Here is a way to do it with substrings:

DECLARE @address NVARCHAR(500) = 'Columbus,OH 43219, United States'

SELECT SUBSTRING(SUBSTRING(SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address)),0,CHARINDEX(',',SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address)))),
    CHARINDEX(' ',SUBSTRING(SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address)),0,CHARINDEX(',',SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address))))) + 1,LEN(@address))

Upvotes: 0

Related Questions