views:

202

answers:

4

How does one detect whether a field is blank (not null) and then select another field if it is?

What I really need is a IsBlank function that works the same as IsNull but with with blanks.

REPLACE doesn't work with blanks, COALESCE only works with NULLS.

+1  A: 

EDIT: You can't use IF() in mssql.

Use an IF statement in the SELECT portion of your SQL:

SELECT IF(field1 != '', field1, field2) AS myfield FROM ...
MDCore
Nice, but if I need to work more than 2 columns will be a pain.
graham.reeds
I can't seem to get it working: `SELECT IF(Address2!='', Address2, Address3) AS Address FROM Sites` gives Incorrect syntax near the keyword 'IF'.
graham.reeds
Okay, I'm a total dumbass and getting confused with mysql. `IF()` only seems to work with mysql, so rather use `CASE WHEN THEN ELSE END` as above. I'll leave this here as a warning to others :)
MDCore
+4  A: 

You can use a CASE statement for this

select 
Case WHEN Column1 = '' OR Column1 IS NULL OR LEN (TRIM (Column1))  = 0 
     THEN Column2 
     ELSE Column1 END as ColumnName
from TableName
Raj More
This is what I had got on my own but is very ungainly.
graham.reeds
+2  A: 

You could always write an isBlank() function, something like

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION isBlank
(
    @CheckExpression varchar, @ReplacementExpression varchar
)
RETURNS varchar
AS
BEGIN
    IF @CheckExpression IS NOT NULL
    BEGIN
     IF @CheckExpression='' or LEN(@CheckExpression) = 0
  RETURN @ReplacementExpression
 ELSE
  RETURN @CheckExpression
    END

    RETURN @ReplacementExpression
END
GO
Chris Welsh
I assume you could make it support *N* number of expressions since COALESCE does anyway via case statement like Raj More.
graham.reeds
+3  A: 

How about combining COALESCE and NULLIF.

SELECT COALESCE(NULLIF(SomeColumn,''), ReplacementColumn)
FROM SomeTable
PHeiberg
Interesting...can it be easily extended to work with 3 columns?
graham.reeds
Answer - yes you can: `COALESCE(NULLIF(Address2,''), NULLIF(Address3,''), Address4)`
graham.reeds
Moving the answer to yours as it is more elegant than the Case statement.
graham.reeds