Hello,

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 would be gratefull if
someone could help me out. I don't want to leave out the NULL values
using WHERE Email is NOT NULL, I just want to convert them to an empty
string ''

Many thanks in advance :-)

Yas

Yas (yasar1 (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx