March 1, 2012 at 6:49 pm
Having some trouble figuring this one out. I've got two tables: Contacts & ContactsNew. Contacts has a column that allows 16 characters named PhoneOld. ContactsNew contains a similar column that allows only 10 characters named PhoneNew.
Here is some data found in Contacts.dbo.PhoneOld (16 characters max):
(555) 555-5555
555.555.5555
555/555/5555
Here is what I want in ContactsNew.dbo.PhoneNew (10 characters max):
5555555555
Using the existing data in PhoneOld, how do I insert the phone number into PhoneNew while stripping the useless characters in order to stay within the allotted 10 characters? Changing the max char count is not an option.
Here's an example of what I have so far:
INSERT INTO
ContactsNew
(PhoneNew)
VALUES
(SELECT PhoneOld FROM Contacts WHERE ContactID = 1)
I've tried using REPLACE and TRIM but can't seem to list multiple characters to prohibit while maintaining the actual phone numbers. Any help is appreciated. Thanks!
March 1, 2012 at 7:01 pm
How about multiple REPLACEs?
select replace( replace( replace( replace( replace( replace (Numb, '.','') , '-','' ) , '/','') , ' ','') , ')','') , '(','')
from
(
select '(555) 555-5555'
union all select '555.555.5555'
union all select '555/555/5555' ) Phones( Numb)
March 1, 2012 at 7:23 pm
Thanks Coffee. I actually just figured this out literally seconds before you posted this! Thanks again!
Close the thread, mods.
March 1, 2012 at 7:33 pm
y'r welome 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply