CODEWITHSUNDEEP
sqlsql-server
Vik
Vik

Reputation: 89

Concat two columns with leading zeroes in the second column value, SQL

I am trying to concat values from two columns with a specific format for the second column value. The second column value should be in "0000" format. I have my current query like this

SELECT 
     ISNULL(BatchID,'')+ISNULL(UID,'') as BatchIDUID 
FROM 
     myTable 
WHERE 
     id=249

which gives me output 

1509161               //BatchID = 150961 and UID = 1

it should be in the format 

1509160001            //BatchID = 150961 and UID = 1
1509160022            //BatchID = 150961 and UID = 22
1509160333            //BatchID = 150961 and UID = 333
1509164444            //BatchID = 150961 and UID = 4444

Upvotes: 1

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

As a note, you can do this using arithmetic and not converting the values to strings:

select COALESCE(BatchId, 0) * 10000 + COALESCE(UId, 0)

You can then cast this as a string:

select CAST(COALESCE(BatchId, 0) * 10000 + COALESCE(UId, 0) as VARCHAR(10))

This happens to work for your values, which fit in an integer. However, you can support longer values by using decimals:

select COALESCE(CAST(BatchId as DECIMAL(20, 0)), 0) * 10000 + COALESCE(UId, 0)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175776

Use RIGHT

SELECT 
     ISNULL(BatchID,'')+ISNULL(RIGHT('0000' + UID, 4),'') as BatchIDUID 
FROM myTable 
WHERE  id=249;

General approach:

DECLARE @pad INT = 4;
SELECT RIGHT(REPLICATE('0', @pad) + /* column/variable */, @pad);

Upvotes: 3

Related Questions