Left Padding is there any built in functions
-
I have a developer who is writing a function to mimick Oracle's LPAD function. Here is the code. How else could these be done?
CREATE FUNCTION LPAD(
@Char1 VARCHAR(8000),
@n BIGINT,
@Char2 VARCHAR(255))
RETURNS VARCHAR(8000)
AS
BEGIN
IF DATALENGTH(@Char1) > @n
BEGIN
IF SIGN(@n) = -1
RETURN SUBSTRING(@Char1, 1, 0)
RETURN SUBSTRING(@Char1, 1, @n)
END
RETURN REPLICATE(@Char2, @n - DATALENGTH(@Char1)) + @Char1
END
Thanks
Steve
-
Try something like this
DECLARE @var VARCHAR(15)
SET @var = 1
SELECT CAST(REPLACE(STR(RTRIM(LTRIM(@var)), 15, 0), ' ', 0) AS VARCHAR(15))
Good Hunting!AJ Ahrens
webmaster@kritter.net -
LEFT(ISNULL(REPLICATE(@Char2, @n - DATALENGTH(@Char1)),'') + @Char1,(CASE WHEN @n < 0 THEN 0 ELSE @n END))
Far away is close at hand in the images of elsewhere.
Anon. -
I have this script in my archives...
CREATE FUNCTION LPAD(
@Char1 VARCHAR(8000),
@n INT,
@Char2 VARCHAR(255))
RETURNS VARCHAR(8000)
AS
BEGIN
IF DATALENGTH(@Char1) > @n
BEGIN
IF SIGN(@n) = -1
RETURN SUBSTRING(@Char1, 1, 0)
RETURN SUBSTRING(@Char1, 1, @n)
END
RETURN REPLICATE(@Char2, @n - DATALENGTH(@Char1)) + @Char1
END
Conversely I also have...
CREATE FUNCTION RPAD(
@Char1 VARCHAR(8000),
@n INT,
@Char2 VARCHAR(255)
 
RETURNS VARCHAR(8000)
AS
BEGIN
IF DATALENGTH(@Char1) > @n
BEGIN
IF SIGN(@n) = -1
RETURN SUBSTRING(@Char1,1,0)
RETURN SUBSTRING(@Char1,1,@n)
END
RETURN @Char1+ REPLICATE(@Char2,@n-DATALENGTH(@Char1))
END
Hope they work as I have not used them yet but thought that they might be useful at some point.
--Lori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply