If someone is still interested, I found this article on DATABASE.GUIDE:
Left Padding in SQL Server – 3 LPAD() Equivalents
In short, there are 3 methods mentioned in that article.
Let's say your id=12 and you need it to display as 0012.
Method 1 – Use the RIGHT() Function
The first method uses the RIGHT() function to return only the rightmost part of the string, after adding some leading zeros.
SELECT RIGHT('00' + '12', 4);
Result:
0012
Method 2 – Use a Combination of RIGHT() and REPLICATE()
This method is almost the same as the previous method, with the only difference being that I simply replace the three zeros with the REPLICATE() function:
SELECT RIGHT(REPLICATE('0', 2) + '12', 4);
Result:
0012
Method 3 – Use a Combination of REPLACE() and STR()
This method comes from a completely different angle to the previous methods:
SELECT REPLACE(STR('12', 4),' ','0');
Result:
0012
Check out the article, there is more in depth analysis with examples.
A simple example would be
DECLARE @number INTEGER
DECLARE @length INTEGER
DECLARE @char NVARCHAR(10)
SET @number = 1
SET @length = 5
SET @char = '0'
SELECT FORMAT(@number, replicate(@char, @length))
This is what I normally use when I need to pad a value.
SET @PaddedValue = REPLICATE('0', @Length - LEN(@OrigValue)) + CAST(@OrigValue as VARCHAR)
More efficient way is :
Select id, LEN(id)
From TableA
Order by 2,1
The result :
id
----
1
2
12
123
1234
Something fairly ODBC compliant if needed might be the following:
select ifnull(repeat('0', 5 - (floor(log10(FIELD_NAME)) + 1)), '')
+ cast (FIELD as varchar(10))
from TABLE_NAME
This bases on the fact that the amount of digits for a base-10 number can be found by the integral component of its log. From this we can subtract it from the desired padding width. Repeat will return null for values under 1 so we need ifnull.
I created a function:
CREATE FUNCTION [dbo].[fnPadLeft](@int int, @Length tinyint)
RETURNS varchar(255)
AS
BEGIN
DECLARE @strInt varchar(255)
SET @strInt = CAST(@int as varchar(255))
RETURN (REPLICATE('0', (@Length - LEN(@strInt))) + @strInt);
END;
Use: select dbo.fnPadLeft(123, 10)
Returns: 0000000123
-- Please look into these.
select FORMAT(1, 'd4');
select FORMAT(2, 'd4');
select FORMAT(12, 'd4');
select FORMAT(123, 'd4');
select FORMAT(1234, 'd4');
-- I hope these would help you
Create Function :
Create FUNCTION [dbo].[PadLeft]
(
@Text NVARCHAR(MAX) ,
@Replace NVARCHAR(MAX) ,
@Len INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @var NVARCHAR(MAX)
SELECT @var = ISNULL(LTRIM(RTRIM(@Text)) , '')
RETURN RIGHT(REPLICATE(@Replace,@Len)+ @var, @Len)
END
Example:
Select dbo.PadLeft('123456','0',8)
declare @T table(id int)
insert into @T values
(1),
(2),
(12),
(123),
(1234)
select right('0000'+convert(varchar(4), id), 4)
from @T
Result
----
0001
0002
0012
0123
1234
I needed this in a function on SQL server and adjusted Patrick's answer a bit.
declare @dossierId int = 123
declare @padded_id varchar(7)
set @padded_id = REPLACE(
SPACE(7 - LEN(@dossierId)) + convert(varchar(7), @dossierId),
SPACE(1),
'0')
SELECT @dossierId as '@dossierId'
,SPACE(LEN(@dossierId)) + convert(varchar(7)
,@dossierId) as withSpaces
,@padded_id as '@padded_id'
This works for strings, integers and numeric:
SELECT CONCAT(REPLICATE('0', 4 - LEN(id)), id)
Where 4 is desired length. Works for numbers with more than 4 digits, returns empty string on NULL value.
Old post, but maybe this helps someone out:
To complete until it ends up with 4 non-blank characters:
SELECT RIGHT ('0000'+COLUMNNAME, 4) FROM TABLENAME;
To complete until 10:
SELECT RIGHT ('0000000000'+COLUMNNAME, 10) FROM TABLENAME;
In case the column is numeric, convert it to varchar first with such code:
Select RIGHT('0000'+Convert(nvarchar(20), COLUMNNAME), 4)
From TABLENAME
And to complete until 10 with a numeric field:
SELECT RIGHT ('0000000000'+Convert(nvarchar(20), COLUMNNAME), 10) FROM TABLENAME;
I created a function to do this, where you can specify the desired output character length:
CREATE FUNCTION [dbo].[udfLeadingZero]
(
@String VARCHAR(MAX)
, @Len INT
)
RETURNS VARCHAR(MAX)
BEGIN
SET @String = RIGHT(REPLICATE('0',@Len)+@String,@Len)
RETURN @String
END
GO
My solution is not efficient but helped me in situation where the values (bank cheque numbers and wire transfer ref no.) were stored as varchar where some entries had alpha numeric values with them and I had to pad if length is smaller than 6 chars.
Thought to share if someone comes across same situation
declare @minlen int = 6
declare @str varchar(20)
set @str = '123'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 000123
set @str = '1234'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 001234
set @str = '123456'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 123456
set @str = '123456789'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 123456789
set @str = '123456789'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 123456789
set @str = 'NEFT 123456789'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: NEFT 123456789
SQL Server now supports the FORMAT function starting from version 2012, so:
SELECT FORMAT(id, '0000') FROM TableA
will do the trick.
If your id or column is in a varchar and represents a number you convert first:
SELECT FORMAT(CONVERT(INT,id), '0000') FROM TableA
Try this:
SELECT RIGHT(REPLICATE('0',4)+CAST(Id AS VARCHAR(4)),4) FROM [Table A]
Source: Stackoverflow.com