Question

sqlserver中如何判断一列中当值为一位数时前面插入000,二位数插入00,三位数插入0,四位就不做操作

Answer 1

直接用case语句不就行了?

CREATE TABLE test(id int)

INSERT INTO dbo.test

( id )

SELECT 1

UNION ALL

SELECT

2

UNION ALL

SELECT

10

UNION ALL

SELECT

18

UNION ALL

SELECT

113

UNION ALL

SELECT

117

UNION ALL

SELECT

1011

UNION ALL

SELECT

1200

语句：

1、

SELECT id,sid=CASE WHEN LEN(id)=1 THEN '000'+CAST(ID AS VARCHAR(10))

x05x05x05x05WHEN LEN(id)=2 THEN '00'+CAST(ID AS VARCHAR(10))

x05x05x05x05WHEN LEN(id)=3 THEN '0'+CAST(ID AS VARCHAR(10))

x05x05x05x05WHEN LEN(id)=4 THEN CAST(ID AS VARCHAR(10)) END

FROM test

2、

select id,sid=right('000'+CAST(ID AS VARCHAR(10)),4) from test

结果：

1x050001

2x050002

10x050010

18x050018

113x050113

117x050117

1011x051011

1200x051200

因为我用的int类型,所以类型转化

以上是sqlserver数据库的写法