Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Question on 'case'

    I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
    ex:
    case my_column when null then 'char_string' else my_column end 'column'

    I run it and the nulls are still in my_column. Does case not work on null values this way?
    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Question on 'case'

    Originally posted by exdter
    I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
    ex:
    case my_column when null then 'char_string' else my_column end 'column'

    I run it and the nulls are still in my_column. Does case not work on null values this way?
    Thanks.
    Try this:

    case when my_column is null then 'char_string' else my_column end 'column'

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: Question on 'case'

    Can I use an isnull here? Doesn't matter. Your solution worked. Thanks alot.
    Last edited by exdter; 01-29-04 at 12:07.

  4. #4
    Join Date
    Jan 2004
    Posts
    35
    Use isull(mycolumn,'CharString')

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    Thats what I thought too but I used Snails method already.
    Thanks.

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    I tried it this way too and it works fine and is much neater.
    Thanks.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    coalesce(mycolumn,'charstring') is also much neater and it uses standard sql

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

  8. #8
    Join Date
    Aug 2003
    Posts
    328
    Thanks r937!
    Last edited by exdter; 01-29-04 at 12:58.

Posting Permissions

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