SQL Server将字符串列值连接为5个字符长
【腾讯云】亏本大甩卖,服务器4核16G 1年370元(带宽12M,系统盘120GB SSD盘,月流量2000GB)!!!!!!
云产品 配置 价格
服务器 1核2G,带宽5M,系统盘50GB SSD盘,月流量500GB 38元/年
MySQL 1核1G 19元/年
服务器 16核32G,带宽18M,系统盘250GB SSD盘,月流量5000GB 1197元/年
点我进入腾讯云,查看更多详情

Scenario:

I have a table1(col1 char(5)); A value in table1 may '001' or '01' or '1'.

Requirement:

Whatever value in col1, I need to retrive it in 5 char length concatenate with leading '0' to make it 5 char long.

Technique I applied:

select right(('00000' + col1),5) from table1; 

I didn't see any reason, why it doesn't work? but it didn't. Can anyone help me, how I can achieve the desired result?

#0

Since you're using a fixed width column, it's already of size 5 (with whitespace). You need to trim it:

DECLARE @table1 TABLE (col1 char(5))

INSERT INTO @table1 (col1) VALUES ('12345')
INSERT INTO @table1 (col1) VALUES ('1')

SELECT RIGHT('00000'+RTRIM(col1),5) FROM @table1

-- Output:
-- 12345
-- 00001

Or use varchar instead:

DECLARE @table2 TABLE (col1 varchar(5))

INSERT INTO @table2 (col1) VALUES ('12345')
INSERT INTO @table2 (col1) VALUES ('1')

SELECT RIGHT('00000'+col1,5) FROM @table2

-- Output:
-- 12345
-- 00001

#1

If you are storing the data in a CHAR field you are probably getting right spaces buffered with blanks. e.g. 01 = "01 ". If your do a RIGHT("00000" + value, 5) it'll still be the original value. You need to do a RTRIM() on the value or store the data in a VARCHAR field.

#2

you need to store your data in a consistent manner, so you don't need to write queries to format the data each time. this will fix your existing data:

UPDATE table1
    SET col1= RIGHT('00000'+ISNULL(RTRIM(col1),''),5)

now every time you select you only have to do this:

SELECT col1 FROM table1

however, you must make sure that the data is formatted properly (leading zeros) every time it is inserted. I'd add a check constraint just to make sure:

ALTER TABLE table1 ADD CONSTRAINT
    CK_table1_col1 CHECK (LEN(col1)=5)

and when you insert do this:

INSERT INTO table1
        (col1, ...
    VALUES
        (RIGHT('00000'+ISNULL(RTRIM(@col1),''),5)

#3

The problem is that the char(5) field is always 5 characters long, not matter what you put into it. If you insert '01' into the field, the value stored is actually '01   ' (note the trailing spaces).

Try this:

select right(('00000' + replace(col1, ' ', '')), 5)

Edit: I will leave my answer here as an example, but Michael's answer using rtrim is better.

推荐文章

如何检查Web部件性能?

如何检查Web部件性能?

推荐文章

自定义jquery easing函数的帮助

自定义jquery easing函数的帮助

推荐文章

Jscrollpane和内部锚链接

Jscrollpane和内部锚链接

推荐文章

关于CSS的问题宽度:100%

关于CSS的问题宽度:100%

推荐文章

如何禁用PHP中的“e”PREG_REPLACE_EVAL修饰符?

如何禁用PHP中的“e”PREG_REPLACE_EVAL修饰符?

推荐文章

Javascript在不同浏览器中的功能有何不同?

Javascript在不同浏览器中的功能有何不同?

推荐文章

Android活动导航流问题

Android活动导航流问题

推荐文章

性格问题

性格问题

推荐文章

VS 2008附加到进程已禁用

VS 2008附加到进程已禁用

推荐文章

Bzr:由于UTF-8错误,无法导入Mercurial(Hg)存储库

Bzr:由于UTF-8错误,无法导入Mercurial(Hg)存储库

推荐文章

按分组按子句排序

按分组按子句排序

推荐文章

不会报告jQuery ajax中的错误

不会报告jQuery ajax中的错误

推荐文章

Django复选框SelectMultiple小部件将-----值添加到查询集

Django复选框SelectMultiple小部件将-----值添加到查询集

推荐文章

函数计数调用

函数计数调用

推荐文章

UITableView滚动崩溃应用

UITableView滚动崩溃应用

推荐文章

jquery函数load()无法正确加载dojo内容

jquery函数load()无法正确加载dojo内容