Re: zero padding

From: Pranay Pandya (ppandya_at_orthodon.com)
Date: 10/14/04

  • Next message: Alkaline: "SQL Server Data Administration over the web" 
    Date: 14 Oct 2004 05:42:56 -0700

    CREATE FUNCTION udf_ZeroPaddingBack (@VarcharIn char(50),
    @RequiredLength int)
    RETURNS varchar(50)
    AS BEGIN
            DECLARE @VarcharOut Varchar(50)
            DECLARE @CurrentLength int
            SET @CurrentLength = LEN(@VarcharIn)
            select @VarcharOut = @VarcharIn
            WHILE @CurrentLength < @RequiredLength
            BEGIN
                    SET @VarcharOut = LTRIM(RTRIM((CONVERT(VARCHAR(50), @VarcharOut))))
    + Convert(varchar(1),'0')
                    SET @CurrentLength = LEN(@VarcharOut)
            END
            RETURN LTRIM(RTRIM(@VarcharOut))
    END

    bb_43@hotmail.com (Brian Bunin) wrote in message news:<n7ibd.3497$6q2.2650@newssvr14.news.prodigy.com>...
    > In article <4a2a3d1f.0410131211.74b13e6c@posting.google.com>, ppandya@orthodon.com (Pranay Pandya) wrote:
    > >Below is a function i created for adding zero padding. The function
    > >works if i want to add '0' padding at the back.
    > >for example test00000.
    > >But if i change the function to make it pad '0's in the front it does
    > >not work.
    > >for example 00000test DOES NOT work. The commented code in the
    > >function below does not work.
    > >CREATE FUNCTION udf_ZeroPadding(@VarcharIn char(50), @RequiredLength
    > >int)
    > >RETURNS varchar(50)
    > >AS BEGIN
    > > DECLARE @VarcharOut char(50)
    > > DECLARE @CurrentLength int
    > > SET @CurrentLength = LEN(@VarcharIn)
    > > select @VarcharOut = @VarcharIn
    > > WHILE @CurrentLength < @RequiredLength
    > > BEGIN
    > > --SET @VarcharOut = '0' + @VarcharOut
    > > --The above line does not work.
    > > SET @CurrentLength = LEN(@VarcharOut)
    > > END
    > > RETURN LTRIM(RTRIM(@VarcharOut))
    > >END
    > >
    > >Thank You,
    > >-Pranay
    >
    >
    > Convert(varchar(1),'0')

  • Next message: Alkaline: "SQL Server Data Administration over the web"
    Loading