Try this
UPDATE people
SET phone = CASE
WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20)
WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20)
ELSE phone
END
FROM people
WHERE LEFT(phone,3) = '405'
AND LENGTH(phone) > 7;
This way you don't accidentally replace '405' contained in the rest of
the phone number. Also, if the phone numbers contain punctuation you
will need to change the '7' in the LENGTH criteria. You will have to
replace 'people' and 'phone' with the appropriate table and column name
respectively.
-----Original Message-----
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: Friday, January 06, 2006 8:09 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - concat string and update question - Found word(s)
remove list in the Text body
I have a table of people and their phone numbers, some have the area
code and others do not. Everyone in this table lives in the same area
code, so I would like to remove the area code from the phone number
field. Basically replace '(405)' or '405-' with '' is there an easy way
to do that in a query with out writing code? I know how to do it with
code but would like an easier way if some one knows the SQL better than
I do.
--
Chris W
KE5GIX
Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
