Post reply

Text function in T_sql

  • January 20, 2004 at 10:27 am

    #84358

    Hello!

    In my query I'm suppose to have a lot of new items, and I was wondering how I can receive the following:

     
    Format(i, "0000")  would return 0001 if i = 1 and 0010 if i = 10.
     
    Is this possible in t_sql?

    --


    -Lars

    Please only reply to this newsgroup. All mails would be bounced back.

  • January 20, 2004 at 11:08 am

    #490871

    I use a little udf like

    Create Function fn_LPadZero

     (@Num varchar(20),

      @Length int)

    Returns VarChar(20)

    -- Returns passed integer value left-padded with zeros for length of passed @Length

    -- ie passed value of 123,6 becomes "000123"

    -- Print dbo.fn_LPadZero(-123, 6)

    Begin

     declare @Result varchar(20)

     if left(ltrim(@Num),1) = '-' begin

      set @Result = '-' + right('00000000000000000000' + replace(convert(varchar(20), @Num), '-', ''), @Length-1)

     end else set @Result = right('00000000000000000000' + convert(varchar(20), @Num), @Length)

     return @Result

    End

     
    Note: UDF is slower than having the builtin SQL functions "inline". so if you're getting larger number of records, use the buildin functions and CASE sytax instead of a UDF call.
     



    Once you understand the BITs, all the pieces come together

  • January 20, 2004 at 11:12 am

    #490874

    Thanks - but here is a similar function; and easier to understand

    SELECT RIGHT(Replicate('0',5) + CAST(512 AS varchar(5)), 5)

     


    -Lars

    Please only reply to this newsgroup. All mails would be bounced back.

  • January 20, 2004 at 11:23 am

    #490878

    Thanks Lars

    Yes, can be simplified... just remember to deal with negative values if appropriate.

     



    Once you understand the BITs, all the pieces come together

  • January 21, 2004 at 7:05 am

    #490968

    For non negative values I prefer

    REPLACE(STR(512,15,0),' ','0')

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 21, 2004 at 7:52 am

    #490980

    Lars, try the various methods. depending on your data, the REPLACE() may run 5-10 time slower than the REPLICATE() or RIGHT(). Also consider if -1 = '-00001' or '0000-1' for negatives or other non-positive integer data you might pass the function.



    Once you understand the BITs, all the pieces come together

  • August 30, 2005 at 5:14 pm

    #586319

    Works like a charm.  Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply