• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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=?



0
VBdotnet2005
Asked:
VBdotnet2005
  • 3
  • 2
  • 2
  • +2
1 Solution
 
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 & ArchitectCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 & ArchitectCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now