Rpad and Lpad Equivalent in T-SQL
Somehow, there’s no built-in function equivalent to Oracle’s LPAD or RPAD in SQL. There are several ways to do this, in fact, I posted another way to do RPAD at http://rickcaminiti.com/bi/t-sql-equivalent-of-oracles-rpad/. Unfortunately, this leaor
ves no way to do an LPAD.
So here’s the scenario: You have, let’s say an account number that is stored as a number in one system and as a char in another system with leading zeros. So, 123 might look like 000123 in the other system. So if you need to represent 123 as 000123, you need to turn it into a char(6) and add 3 zeros to the begining. That’s exactly what this LPAD function would do.
CREATE FUNCTION LPAD
(@SourceString VARCHAR(MAX),
@FinalLength INT,
@PadChar CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
(SELECT Replicate(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)
END
GO
REATE FUNCTION RPAD
(@SourceString VARCHAR(MAX),
@FinalLength INT,
@PadChar CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
(SELECT @SourceString + Replicate(@PadChar,@FinalLength - Len(@SourceString)))
END
GO
In the example above, you would accomplish this like so:
EXEC dbo.LPAD(Cast(123 AS VARCHAR),6,'0')
OR
SELECT NewString = dbo.LPAD(Cast(123 AS VARCHAR),6,'0')
Here’s where I found it. http://www.sqlusa.com/bestpractices2005/padleadingzeros/
Recent Buzz