update partial string

#1

Hi,
I need to update the phone numbers in a table. if the first 7 numbers
are "(909)222-", I need to change them to "(909)333-", regardless what
the last 4 numbers are.

I can select the records easily:
select phone from employee
where phone like "(909)222-%'

but how do I update all the records in one T-SQL statement? I don't
think I can use % in my update statement?

Thank you in advance.

#2

Eddie wang wrote:

Quote:

I can select the records easily:
select phone from employee
where phone like "(909)222-%'

but how do I update all the records in one T-SQL statement? I don't
think I can use % in my update statement?

update employee set substring(phone,6,3)='333' where phone like "(909)222-%'

or

update employee set substring(phone,locate(phone,'222-'),3)='333' where phone
like "(909)222-%'

bk

#3

update employee set phone = substring(phone,1,5) + '333' + substring(phone,
9, 5) where phone like "(909)222%"

"eddie wang" <eddiekwang (AT) hotmail (DOT) com> wrote

Quote:

Hi,
I need to update the phone numbers in a table. if the first 7 numbers
are "(909)222-", I need to change them to "(909)333-", regardless what
the last 4 numbers are.

I can select the records easily:
select phone from employee
where phone like "(909)222-%'

but how do I update all the records in one T-SQL statement? I don't
think I can use % in my update statement?

Thank you in advance.

#4

Brian,
Thank you for the reply! I didn't know about the "substring" funtion.
It is useful to know.

I ended up using "replace". (I completely forgot about "replace" when
I posted my message).

Thanks again

Brian Keener <bkeener (AT) thesoftwaresource (DOT) com> wrote

Quote:

Eddie wang wrote:
I can select the records easily:
select phone from employee
where phone like "(909)222-%'

but how do I update all the records in one T-SQL statement? I don't
think I can use % in my update statement?

update employee set substring(phone,6,3)='333' where phone like "(909)222-%'

or

update employee set substring(phone,locate(phone,'222-'),3)='333' where phone
like "(909)222-%'

bk

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

update partial string

comp.databases.sybase comp.databases.sybase

update partial string - 08-02-2004 , 02:52 PM

Re: update partial string - 08-02-2004 , 03:51 PM

Re: update partial string - 08-03-2004 , 01:38 PM

Re: update partial string - 08-03-2004 , 04:08 PM