Address - split

Sample : city, state, zip   =   San Francisco, Ca 95555

There is a field in my table call "Home_city_zip".  Is there a way to split it in variables like
San Francisco
ca
95555

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

select Home_city,zip from table where order_id = '12345'
set @city =?
set @state=?
set @zip=?



VBdotnet2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

daveamourCommented:
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
.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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'

Open in new window

0
daveamourCommented:
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
 
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AbarajCommented:
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)

 
0
VBdotnet2005Author Commented:
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 "," ?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
AbarajCommented:
if your data is in predefined format (by any separater, lenght specified), then only we can use substring or any other method.
0
Anthony PerkinsCommented:
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

Open in new window

0
Anthony PerkinsCommented:
This is how I tested it using SQL Server 2008 (you do not need 2008 to execute the function):
Declare @Addresses TABLE(CSZ varchar(100) NOT NULL)

INSERT	@Addresses(CSZ)
VALUES	('San Francisco Ca 95555'),
		('San Francisco, Ca 95555'),
		('San Francisco, Ca 9555-0000')

SELECT	*
FROM	@Addresses
		cross APPLY dbo.udf_GetCSZValues(CSZ)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.