Post reply

Need results in a fixed field length regardless of acutal size

  • March 31, 2006 at 3:07 pm

    #111608

    I have an issue with selecting out a query for a field where I need

    it to be 6 characters long, even if its 3 or 4 or 5.

    COALESCE (CAST(table.value AS CHAR(6)), '')

    this doesn't seem to work properly.

    (in a nutshell the values need to be padded with spaces if the

    values do not equal 6 characters)

    Any suggestions?

  • March 31, 2006 at 3:23 pm

    #630244

    look up stuff in bol

  • March 31, 2006 at 3:26 pm

    #630245

    thats stuff the function

  • April 3, 2006 at 10:40 am

    #630468

    STUFF delete's data (if thats what you were advising). Thks for the craptastic response....

  • April 3, 2006 at 10:51 am

    #630473

    No - he was referring more to the "...and inserts another set of characters at a specified starting point."...

    However, you may be better off using "Space()" for your padding...







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 3, 2006 at 10:57 am

    #630478

    Along these lines...

    DECLARE @string VarChar(6)

    SET @string = 'abcd'

    SELECT 'abcd' + SPACE(6 - LEN(@string)) String, DATALENGTH(@string) BeforePadding, DATALENGTH('abcd' + SPACE(6 - LEN(@string))) AfterPadding







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 3, 2006 at 2:02 pm

    #630521

    Thanks for the reply, I can find nothing better to give me a boost in my day than have someone flame me when I offer asistance that is both free and works. Nice one!

  • April 3, 2006 at 2:49 pm

    #630528

    Thks, I'll try fooling with spacing nested within IF statments, to determine which data rows need the padding.

  • April 3, 2006 at 2:50 pm

    #630529

    Andrew...know how you must feel but if you incorporate it as part of the "Monday Blues" then it makes it easier to live with...

    A...maybe next time you won't be so quick to jump to conclusions...







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 4, 2006 at 4:36 am

    #630605

    Hi,

    Use this Query,

    select 'ABC' + space(6 - len('ABC'))

     

    Regards,

    Amit Gupta,

     

  • April 4, 2006 at 4:59 am

    #630609

    No, but this should work a lot better

    CAST(COALESCE (table.value, '') AS CHAR(6))

    In your one, if value had been null you'd have got an empty string. SPACE will also work, as will STUFF. I tested this on sysobjects (select cast(name as CHAR(20)) FROM sysobjects) and got as list of object names padded to 20 characters

    p.s. None of us here owe you help, we're not paid for this. Sarcastic comments are unnecessary and uncalled for and will encourage people not to help you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 4, 2006 at 5:37 am

    #630614

    And how is this different from what I posted ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

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

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