• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

how can i update a phone recored in sqlserver (tsql) ?


i have a table with correct and wrong phone numbers.

wrong phone number : i need to delete the third '0' and keep the rest of the number as it is

here there's no 0 before the 7, there's nothing to do.

what would be the query in order to repair this ?

thank you in advance.
Erwin Pombett
Erwin Pombett
  • 3
1 Solution
Mark WillsTopic AdvisorCommented:
Can we rely on length ?

because you could do

select left(phone,3) + right(phone,9)
where substring(phone,4,1) = '0'
and len(phone) = 13

Will have a better go at it in a couple of minutes...
Erwin PombettAuthor Commented:
Hello , yes, i think so ,
i'm goign to pass a query in order to check the lenght...

ok. there's spaces for somme of them, so i will remove teh spaces.
can  the following replace space with empty char ?

then for everbody i can remove the where ?

update person
set MobilePhone = replace(MobilePhone, ' ', '')
where personID = '90882'
Mark WillsTopic AdvisorCommented:
Might be safer to substring() rather than right() and definitely check before you start :)

select phone as OLD, len(phone) as [LEN],left(phone,3) + substring(phone,5,20) as  NEW
from yourtable 
where substring(phone,4,1) = '0'

Open in new window

Add in as much criteria as is revealed in the select before you start with the update. Test before you launch into updates... Take a copy / backup if it is a once off process.

Then (once happy) the update
Update yourtable set phone = left(phone,3) + substring(phone,5,20)
where substring(phone,4,1) = '0'
and len(phone) > 12

Open in new window

Mark WillsTopic AdvisorCommented:
Yep, you can replace(phone,' ','') for all....

EDIT: gotta duck out for an hour....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now