Select column, if blank select from another

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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 ...

Answer 4

How about combining COALESCE and NULLIF.

SELECT COALESCE(NULLIF(SomeColumn,''), ReplacementColumn)
FROM SomeTable