Data triming

[hogan]

Hello, everyone,

I have a problem to clean one of the field called Phonenumber in a database, the values have bracket (), - signe and one extra 1 in front of the phone number,etc. For example: 1-800-567-5555 or (770)604-5437. Does anyone have any good way to make it standard so it looks like 800567555 or 7706045437.

Thanks in advance

[MAK]

Here we go pal.

we had this prob long time back. this is my script wrote long time back.

/* Create table */

create table phone (num varchar(16))

/*Insert values */

insert into phone select "1-800-234-7800"
insert into phone select "(800)234 7800"
insert into phone select "201 234 7800"
insert into phone select "781 774-7800"
insert into phone select "607 284-7800"
insert into phone select "404 238-7800"
insert into phone select "1-900-274-7800"
insert into phone select "900-634-7800"
insert into phone select "900(234)7800"

/*check values*/

select * from phone

/*Select desired values*/
select phon = case left(replace(replace(replace(replace(num,"(&#3 4;,"&#34,"-","&#34," ","&#34,&#34","&#34,1) when
"1" then substring(replace(replace(replace(replace(num,&#34 ;(","&#34,"-","&#34," ","&#34,&#34","&#34,2,10)
else replace(replace(replace(replace(num,"(",&# 34;&#34,"-","&#34," ","&#34,&#34","&#34
end from phone


------------
hogan at 3/13/01 10:44:13 AM

Hello, everyone,

I have a problem to clean one of the field called Phonenumber in a database, the values have bracket (), - signe and one extra 1 in front of the phone number,etc. For example: 1-800-567-5555 or (770)604-5437. Does anyone have any good way to make it standard so it looks like 800567555 or 7706045437.

Thanks in advance

[hogan]

Thank you very much, Mak.

------------
MAK at 3/13/01 11:19:01 AM

Here we go pal.

we had this prob long time back. this is my script wrote long time back.

/* Create table */

create table phone (num varchar(16))

/*Insert values */

insert into phone select "1-800-234-7800"
insert into phone select "(800)234 7800"
insert into phone select "201 234 7800"
insert into phone select "781 774-7800"
insert into phone select "607 284-7800"
insert into phone select "404 238-7800"
insert into phone select "1-900-274-7800"
insert into phone select "900-634-7800"
insert into phone select "900(234)7800"

/*check values*/

select * from phone

/*Select desired values*/
select phon = case left(replace(replace(replace(replace(num,"(&#3 4;,"&#34,"-","&#34," ","&#34,&#34","&#34,1) when
"1" then substring(replace(replace(replace(replace(num,&#34 ;(","&#34,"-","&#34," ","&#34,&#34","&#34,2,10)
else replace(replace(replace(replace(num,"(",&# 34;&#34,"-","&#34," ","&#34,&#34","&#34
end from phone


------------
hogan at 3/13/01 10:44:13 AM

Hello, everyone,

I have a problem to clean one of the field called Phonenumber in a database, the values have bracket (), - signe and one extra 1 in front of the phone number,etc. For example: 1-800-567-5555 or (770)604-5437. Does anyone have any good way to make it standard so it looks like 800567555 or 7706045437.

Thanks in advance