Formation of 20 Character string concatenating by five fields

Question

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

https://stackoverflow.com/questions/5540064/integer-padleft-function-in-t-sql — CodeCaster, May 17 '23 at 10:58
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, May 17 '23 at 12:01

M.Ali · Accepted Answer · 2023-05-17T11:12:54.283

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

SelVazi · Answer 2 · 2023-05-17T11:42:54.793

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

Using FORMAT() function without handling the possible null values in the data will return incorrect string. — M.Ali, May 17 '23 at 11:15

Formation of 20 Character string concatenating by five fields

2 Answers2