How can i select the substring in a string and put it into another field in MS SQL server 2000?

[JonathanJ]

Dear sql ace,
How can i select the substring in a string and put it into another field
in MS SQL server 2000?
For Example, how can i select only "NewYorkCity" in a address field "44WestFourthStreet,NewYorkCity,NY10012"
and put it into another field? because i have to seprate the ONE address
field into few fields, such as zip code, street, city and state.

Best Regards,
Jonathan

[Richard]

Hi,

It seems that the token that separates the substring in your string is a
comma. You can find the location of the comma's by using functions like :
CHARINDEX or PATINDEX. Then you can use the data in between the commas by
using the substring function indicating where to start and where to stop.

i.e.

SELECT CHARINDEX(',', AddresField),
-- this gives you the index of the first comma

CHARINDEX('.', cname, CHARINDEX('.', cname)+1),
-- this gives you the index of the second comma from the beginning

SUBSTRING( AddressField , 1 , CHARINDEX(',', AddressField) - 1),
-- This gives you the string from the beginning until the first comma.

now you must be able to find the next string within the first and the second
comma.

cheers
Richard



"JonathanJ" <twcrazydruid@yahoo.com.tw> wrote:
>
>Dear sql ace,
> How can i select the substring in a string and put it into another field
>in MS SQL server 2000?
> For Example, how can i select only "NewYorkCity" in a address field "44WestFourthStreet,NewYorkCity,NY10012"
>and put it into another field? because i have to seprate the ONE address
>field into few fields, such as zip code, street, city and state.
>
>Best Regards,
>Jonathan