I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:
SOUTH EL MONTE CA91733617
REDWOOD FALLS MN56283
I tryed this:
select substring(cust_shipto_addr_l3, 1, 19) as 'City',
substring(cust_shipto_addr_l3, 20, 21) as 'State',
substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
For some reason, when I run the query I get this for State:
When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.
so if you have substring(cust_shipto_addr_l3, 20, 21) you want the substring beginning at position 20 for a length of 21, not 2. If you only want 2 characters, you should only put a length of 2 characters, not 21. Books online (BOL) really does have some good information! Try it, you might like it!
Thanks for all the help. I was getting the last parameter misxed up. That would actually do the trick.
No it won't...your offsets will all be different
You need a delimeter...space comes to mind but your city names have space in them...and it doesn't appear to have a space between zip and state
And some zips are 9 while others are 5....
The only constant appears to be the space in front of the state....
CREATE TABLE MyTable99(Col1 varchar(8000))
INSERT INTO myTable99(Col1)
SELECT 'SOUTH EL MONTE CA91733617' UNION ALL
SELECT 'BOSSIER LA71172' UNION ALL
SELECT 'GARDENA CA90249107' UNION ALL
SELECT 'MILWAUKEE WI53216' UNION ALL
SELECT 'PARIS IL61944' UNION ALL
SELECT 'DUQUOIN IL62832' UNION ALL
SELECT 'REDWOOD FALLS MN56283' UNION ALL
SELECT 'AUBURN ME04210'
SELECT REVERSE(SUBSTRING(REVERSE(Col1),CHARINDEX(' ',REVERSE(Col1))+1, LEN(Col1)-CHARINDEX(' ',REVERSE(Col1))+1)) AS City
, SUBSTRING(REVERSE(Col1),CHARINDEX(' ',REVERSE(Col1))-2,2) AS State
, REVERSE(LEFT(REVERSE(Col1),CHARINDEX(' ',REVERSE(Col1))-3)) AS Zip
DROP TABLE myTable99