SQL Server Formation of 20 Character string concatenating by five fields [duplicate]

4ngedf3f 于 2023-05-21 发布在其他

关注(0)|答案(2)|浏览(132)

This question already has answers here:

Most efficient T-SQL way to pad a varchar on the left to a certain length? (20 answers)
Closed 4 days ago.

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:-

sql-server

来源：https://stackoverflow.com/questions/76271319/formation-of-20-character-string-concatenating-by-five-fields

2条答案

按热度按时间

jchrr9hc1#

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)
            )

赞(0）回复(0）举报 2023-05-21

vd8tlhqk2#

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

Demo here

赞(0）回复(0）举报 2023-05-21

我来回答

SQL Server Formation of 20 Character string concatenating by five fields [duplicate]

2条答案

相关问题

热门标签

最新问答