Posted by Erland Sommarskog on 07/27/07 21:58

Yas (yasar1@gmail.com) writes:
> I have Table1 with column Email, which has mail addresses in the
> format 'useremailid@'. Few of these Email values are NULL where no
> mail address was specified.
>
> In my View1 I'm using SUBSTRING like... Left(Email,CHARINDEX('@',
> Email)-1) AS EMAIL_NAME (to cut out the @ sign) FROM Table1 and get
> just the usermailid
>
> Anyway, what I would like to do is use the above to get all the
> usermailids and have a condition that checks if Email field IS NULL
> and if TRUE replaces it with blank value ''
>
> So something like IF (Email IS NULL) THEN Email = ''
>
> I've never really used IF ELSE in a query

And you never will, because you can't.

In this particular case, use coalesce:

coalesce(left(email, charindex('@', email) - 1), '') AS EMAIL_NAME

coalesce is a function that accepts two or more values, and returns the
first non-NULL value in the list.

coalesce is a special form of the CASE expression, which you could have
use for in this case. I don't know if your application has a validation
that email really contains a @, but if there isn't you need this:

coalesce(left(Email, CASE WHEN charindex('@', Email) > 0
THEN charindex('@', Email) - 1
ELSE len(Email)
END), '') AS EMAIL_NAME

This is needed, as left() will choke if the second parameter is negative.

The CASE expression - I repeat *expression*, not a statement - evaluates
the conditions in then WHEN clauses in order, and as soon one WHEN
clause is true, the value of the corresponding THEN clause is returned.
If no WHEN clause is true, the value for the ELSE clause is returned.
The ELSE clause may be missing, in which case NULL would be returned.
Note that the data type for the CASE expression is always the one and
the same, regardless of which THEN clause that is returned. Implicit
conversion takes places as needed, and if not possible, you will
get an error.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]