Post reply

Completing zeroes to a field

  • January 2, 2009 at 9:09 am

    #125315

    Hi everyone, I need a little help with writing a query for completing a retrieved field with zeroes to the left. I have a field that is a varchar of 8 but I need to retrieve it like a varchar of 16 but the 8 first characters should be zeroes. Is there an easy way to do this? Like a convert or some function like that?

    Thanks in advance 🙂

  • January 2, 2009 at 9:16 am

    #920343

    could you give us some examples please.

    What would you expect to be returned from the following

    12345678 = 0000000012345678?

    123 = 00000000123 or 0000000000000123

    0 = 000000000 or 0 or 0000000000000000

    etc...

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • January 2, 2009 at 9:24 am

    #920354

    SELECT REPLICATE('0', 16 - LEN(LTRIM(RTRIM('SomeValue'))))

    + LTRIM(RTRIM('SomeValue'))

  • January 2, 2009 at 9:24 am

    #920355

    Christopher Stobbs (1/2/2009)

    could you give us some examples please.

    What would you expect to be returned from the following

    12345678 = 0000000012345678?

    123 = 00000000123 or 0000000000000123

    0 = 000000000 or 0 or 0000000000000000

    etc...

    Thanks

    Chris

    Or null.

    For reference, here's generic left-padding:

    DECLARE @MyColumn VARCHAR(8)

    SET @MyColumn = '23'

    SELECT CAST(RIGHT(REPLICATE('0', 16) + @MyColumn, 16) AS VARCHAR(16))

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 2, 2009 at 9:28 am

    #920358

    I guess it just depends how he wants his output?

    Does he want a value of length 16 or is the max length 16?!?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • January 2, 2009 at 9:31 am

    #920360

    Christopher Stobbs (1/2/2009)

    I guess it just depends how he wants his output?

    Does he want a value of length 16 or is the max length 16?!?

    Could be either! It's far from clear.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 2, 2009 at 9:36 am

    #920366

    Thanks guys for the quick replies. What I wanted is a value of length 16 and the options you gave are great.

    Thank you very much for your help.

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

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