IF OBJECTPROPERTY( OBJECT_ID( 'fn_padl' ), 'IsScalarFunction' ) IS NOT NULL
        DROP FUNCTION fn_padl
go
CREATE FUNCTION fn_padl (
        @SourceString sql_variant,
        @PadLength smallint = null,
        @PadChar nvarchar(5) = N' '
)
RETURNS sql_variant
AS
BEGIN
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                                  */
    /* Created On       : 22 December 2000                                              */
    /* Description      : This function left pads an Ascii or Unicode string based on   */
    /*                the specified length & pad character.                         */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/
    SET @PadLength = COALESCE( @PadLength,
                               CAST( SQL_VARIANT_PROPERTY( @SourceString, 'MaxLength' ) as smallint ) /
                                     CASE
                                        WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'nchar' , 'nvarchar' )
                                        THEN 2
                                        ELSE 1
                                     END )
    RETURN( CASE
                WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'char' , 'varchar' )
                THEN RIGHT( REPLICATE( @PadChar, @PadLength ) +
                                       CAST( @SourceString AS varchar(8000) ) ,
                            @PadLength )
                WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'nchar' , 'nvarchar' )
                THEN RIGHT( REPLICATE( @PadChar, @PadLength ) +
                                       CAST( @SourceString AS nvarchar(4000) ) ,
                            @PadLength )
            END )
END
go
declare @ps varchar( 30 ), @ps1 nvarchar(30)
set @ps = cast( dbo.fn_padl( 'test' , 30 , '*' ) as varchar )
print @ps
set @ps = 'def_test'
print cast( dbo.fn_padl( @ps , default , '_' ) as varchar )
set @ps1 = N'unicode str'
print cast( dbo.fn_padl( @ps1 , default , N'_' ) as nvarchar )
print cast( dbo.fn_padl( 'test' , 10 , default ) as varchar )
print cast( dbo.fn_padl( N'test' , 20 , default ) as nvarchar )

This page was last updated on May 01, 2006 04:28 PM.