Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    9

    Unanswered: concatenate nulls in SQL server

    I have a problem with a view in MS SQL Server 2000. The View concatenates 3 fields (prefix, partnumber, suffix) using hte "+" operator and one or more of these fields may sometimes be null. The problem is that a null value in any of the three fields causes the concatenation to return null even if there are valid values in on or both of the other fields. I thought I might be able to work around this by creating a view containing CASE statements to render the null values as zero length strings, which concatenate properly (e.g., "SELECT CASE WHEN PREFIX IS NULL THEN '' ELSE PREFIX END 'prefix2' FROM [table_name]"). But SQL server will not let me save a view containing a CASE statement. Anybody know how to resolve this problem?

    By the way, I also tried to use UNION views to work around this but SQL server 2000 will not let me save views with UNION sataments even though it runs them properly when views with UNION statements that were created in SQL server 7.0 are imported. What's up with the inability to save a view just because it can't be rendered in the gui pane of the query builder??

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    select isnull(prefix,'')+isnull(partnumber,'')+isnull(suf fix,'') from your table
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I know you can use a function in a view, so perhaps you can write a function that takes as input the three values and internally builds the string using your case logic (or, alternatively, using ISNULL, as in:

    SET @outputstring = ISNULL(@value1,'') + ISNULL(value2,'') + ISNULL(value3,'')

    then just use the function within the view's select statement.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    you can use the function coalesce.
    Code:
      select coalesce(prefix, '') + coalesce(partnumber, '') + coalesce(suffix, '')
      from your_table
    Johan

  5. #5
    Join Date
    May 2004
    Posts
    9
    Thanks for the help. The isnull concatenation works so my (current) problem is resolved. I still don't know why I can no longer save views with UNION statements in them in SQL 2000...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you creating queries in the GUI query builder? You mean the one in Enterprise Manager? It generates crappy code. Plus I don't think you can save the resulting queries as views directly anyway. It's not designed for creating objects, but for viewing data. You can paste the code it creates into Query Analyzer to create your Union query, but clean it up first.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I've often wondered about that, I have done some stuff in views (ordering, is one that leaps to mind) that the enterprise manager screams about, but when I do it through SQL Analyzer, it allows it, and the resulting view SEEMS to work flawlessly...what's UP with that? Is Uncle Billy just trying to save us from ourselves or something?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't ORDER a view unless you include the TOP clause, so it may have screamed about that.

    The GUI "designer" in Enterprise Manager really is only for viewing data. And you have to be carefull even then, 'cause under some circumstances you can accidently change data in the resultset and end up modifying the source data. I generally avoid EM's GUI.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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