October 9, 2014 at 12:48 pm
Hi, I'm working with a field of data that has city, state and zip all on the same column within SQL with no commas. I would like to parse this out into 3 separate columns using a select statement. I'll then use this data to create some SSRS reports.
Here's the column and a sample of data:
regstratn6
ABERDEEN SD 57401-3580
ABINGDON VA 24210-5011
ADAIRVILLE KY 42202
ADAMS TN 37010
ADVANCE MO 63730
AFTON MN 55001
AICA HI 96701
AINSWORTH NE 69210
AIRWAY HEGHTS WA 99001
AITKIN MN 56431
AKRON OH 44311
ALAMEDA CA 94501
ALAMOGORDO NM 88310
ALBANY OR 97321
I have tried the following... I'll need to parse out for all 50 states plus and I'm thinking there must be an easier way to do this.
SELECT regstratn6,
case when regstratn6 like '% or %' then 'OR'
when regstratn6 like '% az %' then 'AZ'
end as State_Name
FROM
where case when regstratn6 like '% or %' then 'OR'
when regstratn6 like '% az %' then 'AZ'
end IN ('or', 'az')
Thanks in advance for any help!
October 9, 2014 at 1:10 pm
This is an option using PARSENAME and some REPLACEs.
WITH SampleData(regstratn6) AS( SELECT
'ABERDEEN SD 57401-3580' UNION ALL SELECT
'ABINGDON VA 24210-5011' UNION ALL SELECT
'ADAIRVILLE KY 42202' UNION ALL SELECT
'ADAMS TN 37010' UNION ALL SELECT
'ADVANCE MO 63730' UNION ALL SELECT
'AFTON MN 55001' UNION ALL SELECT
'AICA HI 96701' UNION ALL SELECT
'AINSWORTH NE 69210' UNION ALL SELECT
'AIRWAY HEGHTS WA 99001' UNION ALL SELECT
'AITKIN MN 56431' UNION ALL SELECT
'AKRON OH 44311' UNION ALL SELECT
'ALAMEDA CA 94501' UNION ALL SELECT
'ALAMOGORDO NM 88310' UNION ALL SELECT
'ALBANY OR 97321'
)
SELECT REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 3), CHAR(7), '.') City,
REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 2), CHAR(7), '.') State,
REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 1), CHAR(7), '.') CP
FROM SampleData
October 9, 2014 at 2:04 pm
Ok, this will work, but I have 6,479 rows and it will increase. Do I do the Union ALL Select 6,479 times?
October 9, 2014 at 2:09 pm
No, I created the CTE with the sample data to have something to run the code on.
You should use your table instead of the CTE.
October 9, 2014 at 2:37 pm
I'm not sure how to write the query. Being that I'm a beginner with SQL I need some help. I tried using my table and I'm getting an error message. Please help with me with how I should write the query.
Here's what I tried:
WITH [MY Table] (regstratn6) as (select)
SELECT REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 3), CHAR(7), '.') City,
REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 2), CHAR(7), '.') State,
REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 1), CHAR(7), '.') CP
FROM [MY Table]
Thanks,
October 9, 2014 at 3:00 pm
You just need this:
SELECT REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 3), CHAR(7), '.') City,
REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 2), CHAR(7), '.') State,
REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 1), CHAR(7), '.') CP
FROM [MY Table]
The WITH part is a common table expression which is defined in here: http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
The best tip I can give you is to trust SQL Server help (aka Books On Line or BOL) and learn how to search in it. All the information is right there and you just need to press F1. Try to look for PARSENAME to understand what it does.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply