UDFs PADL, PADC, PADR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

UDFs PADL, PADC, PADR

Author

Topic

Igor2004
More clever than you

78 Posts

Posted - 2005-06-17 : 22:51:04

[code]
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
-- PADL(@eExpression, @nResultSize [, @cPadCharacter]) -Or-
-- PADR(@eExpression, @nResultSize [, @cPadCharacter]) -Or-
-- PADC(@eExpression, @nResultSize [, @cPadCharacter])
-- Return Values nvarchar(4000)
-- Parameters
-- @eExpression nvarchar(4000) Specifies the expression to be padded.
-- @nResultSize smallint Specifies the total number of characters in the expression after it is padded.
-- @cPadCharacter nvarchar(4000) Specifies the value to use for padding. This value is repeated as necessary to pad the expression to the specified number of characters.
-- If you omit @cPadCharacter, spaces (ASCII character 32) are used for padding.
-- Remarks
-- PADL() inserts padding on the left, PADR() inserts padding on the right, and PADC() inserts padding on both sides.
-- Example
-- declare @gcString nvarchar(4000)
-- select @gcString = 'TITLE'
-- select dbo.PADL(@gcString, 40, default)
-- select dbo.PADL(@gcString, 40, '=')
-- select dbo.PADR(@gcString, 40, '=')
-- select dbo.PADC(@gcString, 40, '+*+')
CREATE function PADC (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
select @length = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nLeftLen smallint, @nRightLen smallint
set @nLeftLen = (@nLen - @length )/2 -- Quantity of characters, added at the left
set @nRightLen = @nLen - @length - @nLeftLen -- Quantity of characters, added on the right
set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cSrting + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
end

return (@cSrting)
end
GO

-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
-- PADL is similar to a function Oracle PL/SQL LPAD
CREATE function PADL (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
select @length = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nLeftLen smallint, @nRightLen smallint
set @nLeftLen = @nLen - @length -- Quantity of characters, added at the left
set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cSrting
end

return (@cSrting)
end
GO

-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
-- PADR is similar to a function Oracle PL/SQL RPAD
CREATE function PADR (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
select @length = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nRightLen smallint
set @nRightLen = @nLen - @length -- Quantity of characters, added on the right
set @cSrting = @cSrting + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
end

return (@cSrting)
end
GO
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources