SQL Extract Values from a String

Question

How do I extract values from a string? I'm trying to separate into 3 new columns. A separate column for city, state and zipcode.

I've tried

select address2,
left(address2, charindex('',address2)-1)
from table

and ---when I try the below code I get "Invalid length parameter passed to the left or substring function"

,LTRIM(substring(a.Address2, CHARINDEX(' ', a.Address2)+1, CHARINDEX(' ', substring(a.address2, charindex(' ',
a.address2)+1, len(a.address2)))-1))

I can break out the city (except for West Warwick) using the following code, but not sure how to make it work for state and zip. This also removes the error.

SUBSTRING(Address2,1,CHARINDEX(' ', a.address2+ ' ')-1) as city

Any ideas what to try?

Nick · Accepted Answer · 2016-06-07 22:30:25Z

4

It looks like your zip codes and your states are all the same length. If that is true, you should be able to use something like this:

SELECT
    LEFT(a.Address2,LEN(a.Address2) - 13) AS City,
    RIGHT(LEFT(a.Address2,LEN(a.Address2) - 11),2) AS State,
    RIGHT(a.Address2,10) AS Zip_Code
FROM
    table;

DEMO CODE

Create the table and data:

CREATE TABLE MyTable (Address2 VARCHAR(100));

INSERT INTO MyTable
VALUES
    ('SAN DIEGO CA 92128-1234'),
    ('WEST WARWICK RI 02893-1349'),
    ('RICHMOND IN 47374-9409');

The query:

SELECT
    LEFT(Address2,LEN(Address2) - 13) AS City,
    RIGHT(LEFT(Address2,LEN(Address2) - 11),2) AS State,
    RIGHT(Address2,10) AS Zip_Code
FROM
    MyTable;

The output:

edited Jun 7, 2016 at 22:30

answered Jun 7, 2016 at 21:50

Nick

7,4912 gold badges25 silver badges46 bronze badges

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

6 Comments

BIReportGuy Over a year ago

Do I have to have a where clause? I still get "Invalid length parameter passed to the LEFT or SUBSTRING function."

Nick Over a year ago

I did update the State logic - I was off by one. If you still get the error, that means there are records where either the state or the zip (or both) are not the same length as those in the example data you provided.

BIReportGuy Over a year ago

This seems to remove the error, but I only know how to make it work for one column. (except West Warwick) SUBSTRING(Address2,1,CHARINDEX(' ', address2+ ' ')-1) as city

Nick Over a year ago

Not sure why you are using SUBSTRING and CHARINDEX as I did not suggest that in my answer. I updated my answer, too, to include each step and the output.

BIReportGuy Over a year ago

I appreciate your answer and your response, and for helping me. I continued to get the error message and so I continued to work at it. The sample data above is only a sub-set to show as an example of what I have to work with. I would need to create a temp table for every city, state and zip in the USA to make this work. I'm not sure I want to do that. Any other suggestions or ways to make this work. Thanks.

|

tarheel · Accepted Answer · 2016-06-10 22:50:45Z

Since you only have 3 parts (City/State/Zip) you can take advantage of a function called parsename in SQL Server 2008 and later. (The original intent of the function is to parse out object names.)

Using a combination of the replace and parsename functions will allow you to be able to separate the data into 3 parts, even if the length of the State (not likely) or the Zip (more likely) change.

Example Data:

create table #my_table
    (
        address2 varchar(75) not null
    )

insert into #my_table values ('CONNERSVILLE IN 47331-3351')
insert into #my_table values ('WEST WARWICK RI 02893-1349')
insert into #my_table values ('RICHMOND IN 47374-9409')
insert into #my_table values ('WILLIAMSBURG IN 47393-9617')
insert into #my_table values ('FARMERSVILLE OH 45325-9226')
--this record is an example of a likely scenario for when the zip length would change.
insert into #my_table values ('WILLIAMSBURG IN 47393')

Solution:

with len_vals as 
    (
        select t.address2
        , len(parsename(replace(t.address2,' ','.'), 1)) as zip_len
        , len(parsename(replace(t.address2,' ','.'), 2)) as st_len
        from #my_table as t
        group by t.address2
    )
select left(a.address2, len(a.address2) - b.zip_len - b.st_len  - 2) as city
, substring(a.address2, len(a.address2) - b.zip_len - 2, b.st_len) as st
, right(a.address2, b.zip_len) as zip_code
from #my_table as a
inner join len_vals as b on a.address2 = b.address2

Results:

Collectives™ on Stack Overflow

SQL Extract Values from a String

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related