英文:
Formation of 20 Character string concatenating by five fields
问题
以下是翻译好的部分：
我想根据以下规则形成一个长度为20个字符的字符串，前面带有零。
我的表格如下：
最多可以在这五个字段中添加0：
CLASS: 4
VENDOR: 5
STYLE: 4
COLOR: 3
SIZE: 4
输出表格如下：
我们是否可以使用SQL查询来完成这个操作？
输出应该如下所示：
英文:
i want to Form a String of length 20 character with preceding zero as per rule as given below.
my table looks like:-
The maximum number of 0 preceed in these five field are
for
CLASS: 4
VENDOR : 5
STYLE : 4
COLOR: 3
SIZE: 4
Output Table looks like:-
Can we do this with sql query.
Please help.
The Output should looks like:-
答案1
得分: 3
Sure, here is the translated code:
SELECT RIGHT('0000' + ISNULL(CAST(CLASS AS VARCHAR(4)), ''), 4)
+ RIGHT('00000' + ISNULL(CAST(VENDOR AS VARCHAR(5)), ''), 5)
+ RIGHT('0000' + ISNULL(CAST(STYLE AS VARCHAR(4)), ''), 4)
+ RIGHT('000' + ISNULL(CAST(COLOR AS VARCHAR(3)), ''), 3)
+ RIGHT('0000' + ISNULL(CAST(SIZE AS VARCHAR(4)), ''), 4)
如果您使用SQL Server 2012及更高版本，您还可以使用 [`CONCAT()`][1] 函数
SELECT CONCAT (
RIGHT(CONCAT('0000', CLASS), 4)
, RIGHT(CONCAT('00000', VENDOR), 5)
, RIGHT(CONCAT('0000', STYLE), 4)
, RIGHT(CONCAT('000', COLOR), 3)
, RIGHT(CONCAT('0000', SIZE), 4)
)
英文:
SELECT RIGHT('0000' + ISNULL(CAST(CLASS AS VARCHAR(4)), '') , 4)
+ RIGHT('00000' + ISNULL(CAST(VENDOR AS VARCHAR(5)), '') , 5)
+ RIGHT('0000' + ISNULL(CAST(STYLE AS VARCHAR(4)), '') , 4)
+ RIGHT('000' + ISNULL(CAST(COLOR AS VARCHAR(3)), '') , 3)
+ RIGHT('0000' + ISNULL(CAST(SIZE AS VARCHAR(4)), '') , 4)
If you are using SQL Server 2012 and later versions, you can also make use of the CONCAT() function
SELECT CONCAT (
RIGHT(CONCAT ( '0000' , CLASS) , 4)
, RIGHT(CONCAT ( '00000' , VENDOR) , 5)
, RIGHT(CONCAT ( '0000' , STYLE) , 4)
, RIGHT(CONCAT ( '000' , COLOR) , 3)
, RIGHT(CONCAT ( '0000' , SIZE) , 4)
)
答案2
得分: 1
以下是翻译好的部分：
使用format()函数是最简单的方法：
select format(CLASS,'0000') as CLASS,
format(VENDOR,'00000') as VENDOR,
format(STYLE,'0000') as STYLE,
format(COLOR,'000') as COLOR,
format(SIZE,'0000') as SIZE
from mytable
如果您想显示00000而不是空值：
select format(coalesce(CLASS,0),'0000') as CLASS,
format(coalesce(VENDOR,0),'00000') as VENDOR,
format(coalesce(STYLE,0),'0000') as STYLE,
format(coalesce(COLOR,0),'000') as COLOR,
format(coalesce(SIZE,0),'0000') as SIZE
from mytable
因此，具有前导零的长度为20个字符的字符串可以是：
select format(coalesce(CLASS,0),'0000') +
format(coalesce(VENDOR,0),'00000') +
format(coalesce(STYLE,0),'0000') +
format(coalesce(COLOR,0),'000') +
format(coalesce(SIZE,0),'0000') as CONCATENAD
from mytable
英文:
The simplest way is to use format() function
select format(CLASS,'0000') as CLASS,
format(VENDOR,'00000') as VENDOR,
format(STYLE,'0000') as STYLE,
format(COLOR,'000') as COLOR,
format(SIZE,'0000') as SIZE
from mytable
If you want to show 00000 instead of nulls then :
select format(coalesce(CLASS,0),'0000') as CLASS,
format(coalesce(VENDOR,0),'00000') as VENDOR,
format(coalesce(STYLE,0),'0000') as STYLE,
format(coalesce(COLOR,0),'000') as COLOR,
format(coalesce(SIZE,0),'0000') as SIZE
from mytable
So the String of length 20 characters with preceding zero could be :
select format(coalesce(CLASS,0),'0000') +
format(coalesce(VENDOR,0),'00000') +
format(coalesce(STYLE,0),'0000') +
format(coalesce(COLOR,0),'000') +
format(coalesce(SIZE,0),'0000') as CONCATENAD
from mytable
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论