IF OBJECTPROPERTY( OBJECT_ID( 'fn_padr' ), 'IsScalarFunction' ) IS NOT NULL
        DROP FUNCTION fn_padr
go
CREATE FUNCTION fn_padr (
        @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 right 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 LEFT( CAST( @SourceString AS varchar(8000) ) +
                                REPLICATE( CAST( @PadChar AS varchar(5) ), @PadLength ),
                          @PadLength )
               WHEN SQL_VARIANT_PROPERTY( @SourceString , 'BaseType' ) IN ( 'nchar' , 'nvarchar' )
               THEN LEFT( CAST( @SourceString AS nvarchar(4000) ) +
                                 REPLICATE( @PadChar, @PadLength ),
                          @PadLength )
    END )
END
go
declare @ps varchar( 30 ), @ps1 nvarchar(30)
set @ps = cast( dbo.fn_padr( 'test' , 30 , '*' ) as varchar )
print @ps
set @ps = 'def_test'
print cast( dbo.fn_padr( @ps , default , '_' ) as varchar )
set @ps1 = N'unicode str'
print cast( dbo.fn_padr( @ps1 , default , '_' ) as nvarchar )
print cast( dbo.fn_padr( N'test' , 20 , N'||' ) as nvarchar )
print cast( dbo.fn_padr( 'test' , default , '_' ) as varchar )

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