Address - split

Ok this is a bit crude and probably needs more thought o make it more robust but it shows you the kinds of things you can do.
Personally I would break these up in the database though into seperate fields
.

This works..

select left(Home_city_zip, charindex(',', Home_city_zip) - 1) city,
replace(replace(Home_city_zip, left(Home_city_zip, charindex(',', Home_city_zip)), ''),
reverse(left(reverse(Home_city_zip), charindex(' ', reverse(Home_city_zip)) - 1)), '') state,
reverse(left(reverse(Home_city_zip), charindex(' ', reverse(Home_city_zip)) - 1)) zip
from table 
where order_id = '12345'

Select allOpen in new window

Hmm where ddi my code go????
Ok here it is again

Declare @TestAddress Varchar(100)
Declare @City Varchar(50)
Declare @State Varchar(2)
Declare @Zip Varchar(6)
Set @TestAddress = 'San Francisco, Ca 95555'
Set @City = SUBSTRING(@TestAddress, 1, CHARINDEX(',', @TestAddress)-1)
Set @State = SUBSTRING(@TestAddress, CHARINDEX(',', @TestAddress) + 2, 2)
Set @Zip = SUBSTRING(@TestAddress, Len(@TestAddress) - 4, 5)
Print @City
Print @State
Print @Zip
 

delcare @city      varchar(50)
delcare @state      varchar(2)
delcare @zip      varchar(6)

select Home_city_zip from table where order_id = '12345'

set @city =left(Home_city_zip , charindex(',', Home_city_zip) - 1)

set @state=substring(Home_city_zip ,(charindex(',', Home_city_zip )+2) , len(Home_city_zip )-charindex(',', Home_city_zip )-6)

set @zip=right(Home_city_zip , 5)

 

I just found out that the data could be "San Francisco Ca 95555' or "San Francisco, Ca 95555" or "San Francisco, Ca 9555-0000"
Hmmm, can I still substring because of "," ?

If you have any standard format which your data follows, then it can be easier to code accordingly..
Since there is no standard way of it, request you to store it in three different columns at least for easier manageability and can append it when required with the required format..

if your data is in predefined format (by any separater, lenght specified), then only we can use substring or any other method.

Try your unformatted addresses with this function:

create FUNCTION [dbo].[udf_GetCSZValues] (
			@Address varchar(50)
			)

RETURNS @CSZValues TABLE (
		    City varchar(50),
		    StateCode char(2),
		    ZIP varchar(50) )
AS 

BEGIN

DECLARE @City varchar(50),
		@StateCode char(2),
		@ZIP varchar(50),
		@Pos tinyint,
        @AddressLen tinyint

SET @Address = RTRIM(@Address)

SELECT	@Pos = CHARINDEX(' ', REVERSE(@Address)),
		@AddressLen = LEN(@Address)
	
IF @Pos > 0
    BEGIN
	SELECT	@ZIP = SUBSTRING(@Address, @AddressLen - @Pos + 2, @AddressLen),
			@Address = RTRIM(LEFT(@Address, @AddressLen - @Pos))
	
	-- First look for ','
	SELECT	@Pos = CHARINDEX(',', REVERSE(@Address)),
			@AddressLen = LEN(@Address)

	IF @Pos = 0
			-- Look for ' '
			SET @Pos = CHARINDEX(' ', REVERSE(@Address))
			
	IF @Pos > 0
	    SELECT  @StateCode = RIGHT(@Address, 2),
				@City = RTRIM(LEFT(@Address, @AddressLen - @Pos))
    END

INSERT	@CSZValues(City, StateCode, ZIP) 
VALUES	(@City, @StateCode, @ZIP)

RETURN

END

Select allOpen in new window

Oops I forgot the output (I am sorry for the multiple posts):

CSZ	City	StateCode	ZIP
San Francisco Ca 95555	San Francisco	Ca	95555
San Francisco, Ca 95555	San Francisco	Ca	95555
San Francisco, Ca 9555-0000	San Francisco	Ca	9555-0000

Select allOpen in new window