Post reply

Inserting Leading Zeros into columns

  • September 16, 2003 at 11:59 am

    #159913

    Hi. I have a SQL Table with 4 columns of telephone numbers but all are missing the leading zeros (and they are not in the source file used for the upload). What is the best command to pad all records in the approriate colums (assume the columns are called telephone1 telephone2 etc).

    Thanks. Ralph

  • September 16, 2003 at 3:03 pm

    #474509

    RIGHT(<VARIABLE> + 1000000000000,<NUMBER OF CHARS IN FIELD TO PAD>)

    David Nilsson (recycled)

  • September 16, 2003 at 3:31 pm

    #474515

    How about:

    RIGHT('00000000000000000' + <variable>, <Number of chars in field>)

    This is provided the telephone numbers are character fields and not numeric fields.

  • September 16, 2003 at 3:49 pm

    #474518

    I think I would use the STUFF function. 

    

    DECLARE @Mask varchar(10)

        , @TelNumber varchar(10)

    

    SET @Mask = '0000000000'

    SET @TelNumber = '1234567'

    

    SELECT STUFF(@Mask, LEN(@Mask) - LEN(@TelNumber), LEN(@TelNumber), @TelNumber)

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer

    Edited by - gljjr on 09/16/2003 3:49:07 PM




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • September 16, 2003 at 5:02 pm

    #474528

    I would use the method suggested by DAVNovak, much easier to understand all that is going on in the statement.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • September 17, 2003 at 4:02 am

    #474591

    Many thanks all for all your help, I used RIGHT etc, though I will take the time to try and understand the STUFF function.

    Regards

    Ralph

  • September 17, 2003 at 12:57 pm

    #474717

    One note: I found an error in my sql. You need to offset the start position 1 character to the right so it should be like the following..

     

    DECLARE @Mask varchar(100)

        , @TelNumber varchar(10)

        , @Start int

    SET @Mask = '0000000000'

    SET @TelNumber = '1234567'

    SET @Start = (LEN(@Mask) - LEN(@TelNumber)) + 1 -- Need to offset it by 1

    SELECT STUFF(@Mask, @Start , LEN(@TelNumber) , @TelNumber) FormattedText

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

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

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