Splitting one column into 3 then inserting into a table

Question

Hello recently I have been trying to split one column containing [city, state zip] and then insert into the same table only in separate columns being [city] [state] [zip].

format of the column varies : [New York, VA 50000]or it could be something like [New York, West Virginia 50000-0000]. Is there an easy way to do this I've tried using sub strings, but I am not having much luck!

left till the 1st `,` reverse the string, left till the 1st space (reverse result) that would give you the city and zip, then use a replace function twice to eliminate the city, zip trim the results and you have your state. Though I'd bet someone could come up with a regular expression that's faster. I'm just not proficient with them. — xQbert, Dec 27 '16 at 19:41

score 0 · Answer 1 · answered Dec 27 '16 at 19:46

0

You can use parsename...

declare @a varchar(100) = 'New York, VA 50000'

select parsename(replace(@a,', ','.'), 2) as [State], parsename(replace(parsename(replace(@a,', ','.'), 1),' ','.'), 1) as ZipCode, parsename(replace(parsename(replace(@a,', ','.'), 1),' ','.'), 2) as City

answered Dec 27 '16 at 19:46

Kannan Kandasamy

13,405
3
25
38

I like the idea, I'm just not sure I see a comma between state and zip and replacing all spaces with ,'s wouldn't work for those multi name cities. or states. unfortunatly I can't get SQL Fiddle to work with SQL server or Id test out my concerns. – xQbert Dec 27 '16 at 20:00
Else we need to use split using http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns/39376747#39376747 and then add into different columns – Kannan Kandasamy Dec 27 '16 at 20:03

score 0 · Answer 2 · answered Dec 27 '16 at 22:12

It will be a little difficult if your "State" names aren't all in 2-letter abbreviations. I would try to do a REPLACE() on those values first to normalize them all, then start separating out the values.

To get the zip codes that are only 5 digits long you can use:

substring(fieldname,len(rtrim(fieldname))-5,5) ... WHERE substring(ZipCode,len(rtrim(zipcode))-5,1)!='-'

Then modify it for the ones that have a dash and additional 4 digits:

substring(ZipCode,len(rtrim(ZipCode))-10,10) ... WHERE substring(ZipCode,len(rtrim(zipcode))-5,1)='-'

You can use the same SUBSTRING() to get the State out, after you set them to the same length. Provided, it's messy but it will get the job done.

score 0 · Answer 3 · answered Dec 28 '16 at 05:26

I tried as follows to solve your requirement:

DECLARE @LOCATION TABLE (LOCATION VARCHAR(200))
DECLARE @NEW_LOCATION TABLE (CITY VARCHAR(200),STATE VARCHAR(200),ZIP VARCHAR(200))

INSERT INTO @LOCATION VALUES('[New York, VA 50000]')
INSERT INTO @LOCATION VALUES('[New York, West Virginia 50000-0000]')

INSERT INTO @NEW_LOCATION 
SELECT REPLACE(SUBSTRING(LOCATION,0,CHARINDEX(',',LOCATION)),'[',''),
        REPLACE(REPLACE(LOCATION,SUBSTRING(LOCATION,0,CHARINDEX(',',LOCATION)+1),''),
                REVERSE(SUBSTRING(REVERSE(LOCATION),0,CHARINDEX(' ',REVERSE(LOCATION)))),''),
        REPLACE(REVERSE(SUBSTRING(REVERSE(LOCATION),0,CHARINDEX(' ',REVERSE(LOCATION)))),']','')
        FROM @LOCATION

SELECT * FROM @NEW_LOCATION

Splitting one column into 3 then inserting into a table

3 Answers3