CODEWITHSUNDEEP
sqlsql-server
Kartik Sharma
Kartik Sharma

Reputation: 47

Prefix 0 in a Phone number string SQL

I have a phone number in sql table, i want to add prefix '0' where ever phone number is 10 digit, but if it is less than 10 or greater than 10 digit then no 0 prefix required.

7863176061
7724269820
2088076157
1992762084
1318912

output

07863176061
07724269820
02088076157
01992762084
1318912

Upvotes: 1

Views: 2585

Answers (4)

KumarHarsh
KumarHarsh

Reputation: 5094

i think this will be fast,

select 

   '0' + cast([phone_number] as varchar(20)) 

from [your_table_name]
where len([phone_number]) = 10 ;

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

Try this:

 select 
   case when len(yourcolumn) =10 
   then '0'+ yourcolumn 
   else yourcolumn end as column 
 from yourtable

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use CASE expression to check the length.

Query

select 
  case when len([phone_number]) = 10 
  then '0' + cast([phone_number] as varchar(20)) 
  else cast([phone_number] as varchar(20)) end
from [your_table_name];

Find demo here

You have to cast the phone number column to varchar if the column datatype is in bigint. Otherwise you can exclude the cast part in the above query.

Upvotes: 0

Pream
Pream

Reputation: 537

Try this

SELECT CASE LEN(Num) WHEN  10 THEN '0'+cast(Num as varchar(11)) ELSE Num END AS Num

Upvotes: 1

Related Questions