Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...

quote:

---

> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

---