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??select isnull(prefix,'')+isnull(partnumber,'')+isnull(suf fix,'') from your table|||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.|||you can use the function coalesce.

select coalesce(prefix, '') + coalesce(partnumber, '') + coalesce(suffix, '')
from your_table|||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...|||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.|||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?|||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.