CODEWITHSUNDEEP
sqlsql-servert-sql
Lookup
Lookup

Reputation: 17

SQL Server - Dynamically Filling the column Values with 0's as per the fixed length

I have a column with the given values 

MRN
1946
456
27
557

The column values length is fixed. If at all any value is less than 6characters,then it should concate 0's to the left and make it 6characters length. The desired output is 

MRN 
001946
000456
000027
000557

Upvotes: 0

Views: 552

Answers (4)

mohan111
mohan111

Reputation: 8865

We can achieve this by adding leading zero's 

select RIGHT('0000'+CAST(MRN AS VARCHAR(10)),6)

Upvotes: 0

ArulPrasath A M
ArulPrasath A M

Reputation: 51

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId
If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId) And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453338

You can also use the FORMAT function for this. (Demo)

SELECT FORMAT(MRN ,'D6')
FROM YourTable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is called left paddings. In SQL Server, this is typically done with more basic string operations:

select right(replicate('0', 6) + mrn, 6)

If mrn is a number, then use the concat() function:

select right(concat(replicate('0', 6), mrn), 6)

Upvotes: 2

Related Questions