如果不存在零，则在返回值上加一个零

Question

以下查询从我的数据库返回以下示例数据。

select mobileno
from Person

样本结果：

775623655
0758956556
0756899955
777325656

如果返回值中没有0，如何在前面添加0？

Answer 1

SELECT CASE LEFT(mobileno,1) 
    WHEN '0' THEN mobileno
    ELSE '0' + isNull(mobileno,'')
    end as mobileno
FROM Person 

Answer 2

select right('0000000000' + mobileno, 10) mobileno from Person

Answer 3

使用REPLICATE:

SELECT 
  RIGHT(REPLICATE('0', 10) + CAST(mobileno AS VARCHAR(10)), 10) AS mobileno
FROM person;

点击此处查看实际操作：

• SQL Fiddle演示

Answer 4

您想确保它总是以0开头，长度是10吗？单向：REPLACE

SELECT REPLACE(STR(mobileno, 10, 0), ' ', '0')
FROM dbo.Person

DEMO

Answer 5

hey检查以下查询。你可以这样做：

declare @table table (temp VARCHAR(MAX))
insert into @table values('775623655')
insert into @table values('0758956556')
insert into @table values('0756899955')
insert into @table values('777325656')
SELECT RIGHT('000000000' + temp , 10)
FROM @table

SqlFiddle