Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Help on replacing characters

Answered Help on replacing characters

  • Tuesday, February 26, 2013 6:17 PM
     
     
    Sign In to Vote
    0



    I have a table with phone numbes

    (000) 000-0000
     NULL
     0000000000
     000-000-0000
    (000)000-0000
    ''


    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






     

All Replies

  • Tuesday, February 26, 2013 6:22 PM
    Moderator
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, February 26, 2013 6:35 PM
     
     
    Sign In to Vote
    0
    Yes , i need only 4 replace statements , no other special characters are there in phone number column
     
  • Tuesday, February 26, 2013 6:37 PM
    Moderator
     
     Proposed Answer Has Code
    Sign In to Vote
    1

    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


     
  • Tuesday, February 26, 2013 6:45 PM
     
     
    Sign In to Vote
    0

    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.

     
  • Tuesday, February 26, 2013 6:45 PM
     
     
    Sign In to Vote
    0
    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
    '.
     
  • Tuesday, February 26, 2013 6:53 PM
    Moderator
     
     Proposed Answer Has Code
    Sign In to Vote
    1

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

    declare @t table (Phone varchar(20))
insert into @t values ('(999) 123-1234'), ('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

     
  • Tuesday, February 26, 2013 6:58 PM
     
     
    Sign In to Vote
    0
    my bad , sorry , it was good ...excellent ..Thank you very much
     
  • Tuesday, February 26, 2013 7:20 PM
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, February 26, 2013 7:32 PM
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, February 26, 2013 7:40 PM
     
     
    Sign In to Vote
    0
    nope , that was syntax error ...
     
  • Tuesday, February 26, 2013 7:45 PM
     
     
    Sign In to Vote
    0

    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 )






     
  • Tuesday, February 26, 2013 7:47 PM
     
     
    Sign In to Vote
    0

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

    Thanks anyways

     
  • Tuesday, February 26, 2013 7:49 PM
    Moderator
     
     
    Sign In to Vote
    1

    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

     
  • Tuesday, February 26, 2013 7:53 PM
     
     Answered
    Sign In to Vote
    1

    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

    • Marked As Answer by coolguy123SQL Tuesday, February 26, 2013 8:12 PM
    •  
     
  • Tuesday, February 26, 2013 8:12 PM
     
     
    Sign In to Vote
    0

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

    this worked for me

     
  • Tuesday, February 26, 2013 8:47 PM
     
     Answered
    Sign In to Vote
    0

    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



    • Marked As Answer by coolguy123SQL Tuesday, February 26, 2013 8:48 PM
    •  
     
  • Tuesday, February 26, 2013 9:05 PM
    Moderator
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, February 26, 2013 9:19 PM
     
     
    Sign In to Vote
    0
    Thanks
     
  • Tuesday, February 26, 2013 9:28 PM
     
     
    Sign In to Vote
    0

    got it,..thanks for the stuff tip

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

     
  • Wednesday, February 27, 2013 11:27 AM
    Owner
     
     
    Sign In to Vote
    0

    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!

     
  • Wednesday, February 27, 2013 12:21 PM
     
     Answered
    Sign In to Vote
    0

    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.

    • Marked As Answer by coolguy123SQL Wednesday, February 27, 2013 2:47 PM
    •