appending zeros to integer

  • MY client has a primary key field that uses integers as the primary key identifier.

    they want fileds that have less than 2 chars to be appending with a zero.

    So for the int field that is 0 that would like it to be 00 or if it is 1 then 01 etc etc.

    How can I accomplish this either in a query or withing the table itself??

    Thanks

  • This sounds like something that should be done via code or SQL as the data is pulled out of the column.  You can set an integer value of 1 to 01, but SQL Server will still store it as 1.  Either change the datatype to Char(2) (or varchar) or do the appending at the presentation layer.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • i would be tempted to do this in the frontend app.


    Everything you can imagine is real.

  • so how would I do this via query??

    i am not sure how to have it only append to the columns where there is only one char?

    I don't really want to change the field type.

  • DECLARE @Ints TABLE (value int)

    SET NOCOUNT ON

    INSERT INTO @Ints

    SELECT '1' UNION ALL

    SELECT '12'

    SELECT CASE LEN(Value) WHEN 1 THEN '0' + CAST(Value as char(2)) ELSE CAST(Value as char(2)) END as 'Value'

    FROM @Ints

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Try;

    SELECT CONVERT (CHAR(2), left( '00', 2-len(<column name&gt) + CONVERT(CHAR(2), <column name&gt)

    FROM ....

    Replace <column name> with the name of the column from the table

  • Damn those 'faces"!!!

    Try;

    SELECT CONVERT (CHAR(2), left( '00', 2-len(<column name&gt ) + CONVERT(CHAR(2), <column name&gt )

    FROM ....

    Replace <column name> with the name of the column from the table

  • Double damn, so much for the "Preview" button!!

    That should be two right brackets in the code.

  • Sounds like a good argument for a client side solution - perhaps a format on the client side?

  • thanks guys I will check it out I won't be able to do that till monday thanks!!!

  • I have had to do this a several times and have found the RIGHT function to be very effective.  For example, for a two character column you could do the following:

    SELECT RIGHT('00' + col, 2) FROM sometable

    But after reading your post again, you probably will have to use a CASE statement because of the rule if the int has < 2 characters:

    SELECT CASE WHEN len(col) < 2 THEN RIGHT('00' + col, 2) ELSE col END FROM sometable

    Of course if all values need to have leading zeros for an INT column you could use:

    SELECT RIGHT('0000000000' + col, 10) FROM sometable

    Hope that helps.

  • Function works:

    create

    function [dbo].[fn_Padd]

    (

    @FileIn int

    )

    returns

    varchar(100)

    as

    begin

    declare

    @newnum as varchar(100),

    @strLen as int,

    @pad as int,

    @padded as varchar(1)

    --think you only want to pad if single digit

    if

    len(@filein) > 1

    begin

    return

    @FileIn

    end

    set

    @newnum = @FileIn

    set

    @strlen = len(@newnum)

    set

    @pad = 100 - @strlen

    set

    @padded = replicate('0',@pad)

    set

    @newnum = @padded + @newnum

    exit_fn:

    return

    @NEWNUM

    end

  • Are we sure you will always have 99 or less?  And where is this being displayed?  There are lots of options with application code. 

Viewing 13 posts - 1 through 12 (of 12 total)

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