Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2005
    Posts
    19

    Unanswered: Leading zero filler help please...

    MSSQL2000
    Brain is overloaded and I'm just not getting this! Ugh! I need a field that will be exported/displayed to contain 10 characters, no spaces. The field I'm extracting is 8 characters and the numerical data (int) is any range up to that. So I have 35795 and need it to be 0000035795 but I could also have a 1057893 and will need it to be 0001057893. I tried various forms of this...

    Select '00' + Right (chk_no, 8)

    and it's just not correct.

    Anyone have a suggestion on what I'm not seeing?
    TIA!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SELECT (CASE WHEN LEN(MYFIELD) = 1 THEN '000000000' + CAST(MYFIELD as varchar)
    WHEN LEN(MYFIELD) = 2 THEN '00000000' + CAST(MYFIELD as varchar)
    etc... END) as MyID
    FROM myTable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Excuse him, he's been smoking crack again

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    SELECT * INTO myTable99 FROM (SELECT 35795 Col1 UNION ALL SELECT 1057893) AS XXX
    GO
    
    SELECT RIGHT(REPLICATE('0',10)+CONVERT(varchar(10),Col1),10) FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    SELECT (CASE WHEN LEN(MYFIELD) = 1 THEN '000000000' + CAST(MYFIELD as varchar)
    WHEN LEN(MYFIELD) = 2 THEN '00000000' + CAST(MYFIELD as varchar)
    etc... END) as MyID
    FROM myTable
    definite candidate for TheDailyWTF.com
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    definite candidate for TheDailyWTF.com
    pish posh. I always forget about replicate and Brett's is better. But it is only 10 digits.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a likely story

    the WTF part was why you would even think to test for LEN

    SELECT right('000000000' + CAST(MYFIELD as varchar),10) as MyID
    FROM myTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    good for you. egg on my face again. hope you feel better about yourself.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Personally, I usually just go for:

    Select right('0000000000' + chk_no, 10)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    hope you feel better about yourself.
    better than ... ?

    hey, man, life is harsh and so is information technology, don't shoot the messenger

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    not the messenger. how the message was phrased. if someone had spoken like that to me in my office I would have told them to come back when they want to speak in a professional tone and they can leave their cute comments at the door.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I know we're all mourning the passing of the corral, but this is just plain ridiculous...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    if someone had spoken like that to me in my office ...
    i guess this isn't your office

    in any case, i apologize

    speaking for myself, whenever i have something i said challenged like that, i try most assuredly to join in the fun, because hey, chances are it was worth a cute remark
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2005
    Posts
    19
    Excellent! Thank you to everyone that responded! r937 - that did it! Blindman, I don't get the leading zeros without the cast (x, as datatype) part.

    I'm not real used to MSSQL, I used to do Oracle stuff and this is just a little different so I'm sure I'll have more questions on syntax and/or whatnot. Thanks again everyone!
    Tiffanie

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks Rudy....

    Blind dudes gotta test more, but I believe "It's a presentation layer" issue covers him.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OH, the reason he doesn't get the zeroes is because of an implicit conversion to int...ain't sql server "smart"?

    Select ISNUMERIC(right('0000000000' + Col1, 10)) FROM myTable99


    Now who was giving me a hard time because I thought implicit conversions suck.

    They are also inconsistent. In some Cases it will do it for you, and in others it won't.

    I had one the other day where I had the same exact syntax in a SELECT that wouldn't work, but did so in the predicate.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •