Post reply

leading blanks

  • May 18, 2010 at 6:19 am

    #224784

    Hi all,

    I want to add leading blanks to the result of a sql query. For example I use the following SQL statement to fill the title with blanks so that the result always has a length of 40:

    select RIGHT(' '+CONVERT(VARCHAR,Title),40) AS Title

    from ADDRESS;

    This works perfectly as long as the collumn "title" is filled with a value. But if the title is "NULL" the resullt is "NULL". Does anyone know how I get 40 blanks as result if the value is "NULL"?

    Thanks,

    Garvin

  • May 18, 2010 at 6:27 am

    #1168753

    i think you jsut need to wrap the Tiutle column with the isnull function:

    select RIGHT(' '+CONVERT(VARCHAR,ISNULL(Title,'')),40) AS Title

    from ADDRESS;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 18, 2010 at 6:29 am

    #1168755

    Hi there, try this:

    SELECT ISNULL(RIGHT(' '+CONVERT(VARCHAR,Title),40) , REPLICATE (' ',40))

    Tell us if that worked for you!

  • May 18, 2010 at 6:46 am

    #1168774

    thanks a lot guys! Both solutions are working.

    regards,

    Garvin

Viewing 4 posts - 1 through 4 (of 4 total)

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