Help on replacing characters

Question

I have a table with phone numbes

[phone]
 NULL
 0000000000
 000-000-0000
[phone]
''

I want to store phone number with no characters , i just want to store

0000000000

can any one help me on providing a script to replace or take out those characters..

Thanks

Answer 1

If you can only have () space and -, then I think you just need 4 replace statements.

Otherwise you may look at my blog post

http://beyondrelational.com/modules/2/blogs/78/posts/11140/cleanse-all-character-fields-in-a-table.aspx

and

http://beyondrelational.com/modules/2/blogs/78/posts/11141/remove-bad-characters-from-a-string.aspx

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Yes , i need only 4 replace statements , no other special characters are there in phone number column

Answer 3

Ok then

select Phone, replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'')
 as CleanedPhone from myTable

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Naomi,looks like error , can you please repost

Msg 403, Level 16, State 1, Line 1

Invalid operator for data type. Operator equals minus, type equals varchar.

Answer 5

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ','), space(1),') as CleanedPhone FROM  tblperson_temp
'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','), space(1),') as CleanedPhone FROM  tblperson_temp
'.

Answer 6

I don't see the error in what I posted.

declare @t table (Phone varchar(20))
insert into @t values ('[phone]'), ('414-530-2289'),('0000000')

select Phone, replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'') as CleanedPhone from @t

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

my bad , sorry , it was good ...excellent ..Thank you very much

Answer 8

can you please help me on updating this table i am trying to update

UPDATE A
SET A.Phone = (select b.Phone, replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'')
as CleanedPhone from dbo.tblperson_temp1 B )
   JOIN tblperson_temp1 A
ON A.personid= B.personid

Answer 9

Hi,

Use your sql query like this:

UPDATE ASET A.Phone = (select b.Phone, replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'') as CleanedPhone )
 JOIN tblperson_temp1 A ON A.personid= B.personid

---

Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

Answer 10

nope , that was syntax error ...

Answer 11

UPDATE A

SET A.Phone = (select b.Phone, replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'') as CleanedPhone )

 from tblperson_temp1 B JOIN tblperson_temp1 A ON A.personid= B.personid

This will not give you any syntax error...try once more !!!

-----------------------

one more thing....why you want to do self join...the same can be achieved in simple sql update code

UPDATE A

SET Phone = (select replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'') as CleanedPhone )

Answer 12

same ... here you are not mentioning B table , so that will cause error ..

Thanks anyways

Answer 13

Why do you need to use two tables here? 

;with cte as (select Phone, PersonID, replace(...) as CleanedPhone from dbo.tblPerson_temp)

update cte

set Phone = cte.CleanedPhone

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 14

use this in your case....

UPDATE A

SET Phone = (select replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'') as CleanedPhone )

---

Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

Answer 15

UPDATE  tblperson_temp1
SET Phone =   replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'')

this worked for me

Answer 16

ya i got that Thanks .... i used this

UPDATE  tblperson
SET Phone =   replace(replace(replace(replace(Phone,'(',''),')',''),'-',''), space(1),'')  


UPDATE  tblperson
SET FAX =   replace(replace(replace(replace(FAX,'(',''),')',''),'-',''), space(1),'') 

to get the phone number and fax in the format of 1234567890 and i would like to know how can i make to

123-456-7890  format , can you please help me

Answer 17

What about my responses to all your other questions (in this and other thread)?

If you want that format, look into STUFF function in BOL.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 18

Thanks

Answer 19

got it,..thanks for the stuff tip

--STUFF( textData, start , length , insertTextData )
SELECT STUFF ((STUFF (phone,4,0,'-' )),8,0,'-') FROM tblperson_temp1

Answer 20

CoolGuy,

I proposed a few of Naomi's posts. Please mark them as answers if they were helpful.

Thanks!

---

Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Answer 21

Including STUFF there's also the SUBSTRING option which is a fairly simple idea:

SELECT SUBSTRING(PHONE,1,3) + '-' + SUBSTRING(PHONE,4,3) + '-' + SUBSTRING(PHONE,7,4)

Just putting it in as an extra option.