SQL Query to concatenate strings or add default value

Question

Hello,

I have to create a View that shows a field created by concatenating some other fields. The simple query that I use is this one:

SELECT     
    CODPROY, DESCPROY, USER, CODPROY + ' - ' + USER + ' - ' + DESCPROY AS Expr
FROM    
    dbo.PROY

The problem is that USER may be NULL and in this case I have to insert a default text, something like 'NOT AVAILABLE'. Can this be done in a view?

Answer 1

Yes, it can be done. You need to use IFNULL (mySQL) or something like it ..

CREATE VIEW foobar as 
SELECT CODPROY
      , DESCPROY
      , USER
      , CODPROY + ' - ' + IFNULL(USER,'DEFAULT') + ' - ' + DESCPROY AS Expr 
 FROM dbo.PROY;

Answer 2

Use coalesce if you are using SQL Server

SELECT 
  CODPROY, 
  DESCPROY, 
  USER, 
  CODPROY + ' - ' + COALESCE(USER,'NOT AVAILABLE') + ' - ' + DESCPROY AS Expr 
FROM dbo.PROY