How do I place leading zeros for numbers less than 10 without affecting those 10+?

  • Probably a newb question, but...

    I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original number if it is 10 or greater (i.e. 1=01, 10=10 and 300=300).

    SELECT DISTINCT RIGHT('0'+CONVERT(VARCHAR,[FacilityCode]),3) FROM...
    

    This returns 1=01, 10=010 and 300=300 (using the same examples as above)

    EDIT: I'm trying to do this within a query so that I can do lookups without altering either table.

    Can someone lend a brother a hand here? My brain just ain't working.

    Any particular reason you don't want `1` to be `001` ?

    RolandoMySQLDBA - The related table has varchar values which follow the example above. Mark, I'm using MSSQL 2008 r2. All... I'm trying to do this through a query in order to select items in a related table which formats the numbers (which are actually varchars) in the manner illustrated above.

  • JNK

    JNK Correct answer

    6 years ago

    The safest way is probably to only add zeroes when the length of the column is 1 character:

    UPDATE
       Table
    SET
       MyCol = '0' + MyCol
    WHERE
       LEN(MyCol) = 1;
    

    This will cover all numbers under 10 and also ignore any that already have a leading 0.

    EDIT

    To just select the data try:

    SELECT
       MyColPadded = CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END
    FROM
       MyTable;
    
  • (it looks from your example code that you are using MS SQL Server, but you should add an appropriate tag (and/or body text) to your question to indicate this if so, or if not which DBMS you are actually using)

    Any of the options above should work fine, I would generally use the "prefix 0s then use RIGHT() option as you have but the others are perfectly valid), but a word of warning if you expect new rows (or updates to existing rows) to contain low numbers - if that is possible then you will end up with unpadded values creeping back in. To avoid this you could use either an AFTER or INSTEAD OF trigger to force values into the right format as they are inserted/updated to stop the problem coming back (without needing to change all code points that save to that table). Be careful to verify performance though: triggers can be very powerful but it is also easy to create a performance nightmare with them.

  • You can use a case statement for this, if it must be formatted in the database and you can't persist it.

    Select case when len(facilityCode) = 1 then 
              '0' else '' end + facilityCode as facilityCode
    From YourTable
    

    It sounds like you are doing this as a FK, if so you can't have a FK constraint on a calculation. To make doing the join easier, you might consider a calculated column.

     ALTER TABLE yourTable 
        ADD cFacilityCode AS (
          case when len(facilityCode) = 1 then '0' else '' end + facilityCode);
    
  • If you wanted a 3 digit number try this.

    SELECT DISTINCT 
    RIGHT('000'+CAST(ISNULL([FacilityCode],0) AS VARCHAR),3) 
    FROM...
    

    Result: 1=001, 10=010 and 300=300

    An 8 digit number would be:

    SELECT DISTINCT 
    RIGHT('00000000'+CAST(ISNULL([FacilityCode],0) AS VARCHAR),8) 
    FROM...
    

    Result: 1=00000001, 10=00000010 and 300=00000300

    This has the advantage of working with subqueries:

    SELECT t_lac.FORCE_OVERWRITE
                , ISNULL(lay.ID,0) AS lacIDLayout
                --, t_lac.lacName
                , CASE WHEN t_lac.lacIDControlType = 18
                    THEN 'PAN' + RIGHT('000' + CAST(ISNULL((SELECT ID FROM Layout l WHERE l.layName = t_lac.lacName),0) AS VARCHAR),3) + '_' + t_lac.lacName
                    ELSE t_lac.lacName
                    END AS lacName
                , t_lac.lacIDControlType
                , t_lac.lacStyle 
                FROM @T_LayoutControl t_lac
                LEFT JOIN Layout lay ON lay.layName = t_lac.layName
    
  • UPDATE MyTable
    SET MyColumn = '0' + Mycolumn
    WHERE MyColumn < '10'
    AND MyColumn NOT LIKE '0%';
    

    The OP stated that the minimum value is 1, but in general, if you want at least two digits, and you have zero, you do want to add another zero, so I would use `LEN` (as in JNK's answer) rather than `LIKE`.

    The logic behind the answer is sane but the answer is not correct for a very different reason than the above comments. `MyColumn` is a varchar, so the condition `MyColumn < '10'` will not result in `TRUE` when the value in the column is `'2'` or `'3'` or ... `'9'`. For char values `'9' < '10'` is false.

    Agree with @ypercube. You could change the first comparison to `MyColumn < 10` but that might break as we don't know if `MyColumn` is *guaranteed* to ever contain only numbers.

  • declare @num varchar(50) = '7'
    select case when @num<100 
                then RIGHT('00'+ CONVERT(VARCHAR(50),@num),2) 
                else CONVERT(varchar(50),@num) end 
    

    welcome to the site. Along with code try to write down some statement for better understanding.

    Also - why use 100 as a cut-off, when 10 would be a better one - only values from 0 through 9 should have an extra zero added.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM