how to check empty field in sql

Question

i have a sql query which shows address1 in a field and address2 as 'address1,address2'.My query is

(S.Address1 + '  ,  ' + S.Address2)as Address1

But when the values in the Address2 is blank it shows 'address1,' and i need to show only address1.How can i remove comma

John Woo · Answer 1 · 2013-04-04T14:09:24.107

5

use COALESCE

(COALESCE(S.Address1,'') + '  ,  ' + COALESCE(S.Address2, '')) as Address1

TSQL COALESCE

but the problem of the statement above is that it will have trailing , when the address2 is null, to fix

(COALESCE(S.Address1,'')  + COALESCE(' , ' + S.Address2, '')) as Address1

UPDATE 1

CASE WHEN S.Address2 = '' 
     THEN S.Address1
     ELSE S.Address1 + '  ,  ' + S.Address2
END  AS Addrss

edited Apr 04 '13 at 14:09

answered Apr 04 '13 at 13:54

John Woo

258,903
69
498
492

+1 for coalesce instead of isnull, since coalesce is sql standand – Adriano Carneiro Apr 04 '13 at 13:56
3

Need to put the comma inside the second `COALESCE`. – Aaron Bertrand Apr 04 '13 at 13:56
2

And due to changing requirements, now we need a `NULLIF` too. *sigh* – Aaron Bertrand Apr 04 '13 at 13:58
1

@Anilkumar you have mentioned in your question that those are `NULL` not blank. – John Woo Apr 04 '13 at 13:58

score 3 · Accepted Answer · answered Apr 04 '13 at 13:58

3

J W is close, I think, but the actual formula you want, I believe, is:

S.Address1 + COALESCE('  ,  ' + NULLIF(S.Address2,''),'') as Address1

(Below not relevant, since OP has now said the column value is blank, not NULL)

But you also have another issue - the above won't work (yet) because you apparently have CONCAT_NULL_YIELDS_NULL set to OFF. This need to be turned on (usually by setting ANSI_NULLS to ON), since:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

answered Apr 04 '13 at 13:58

Damien_The_Unbeliever

234,701
27
340
448

can u tell me the exact difference between COALESCE and ISNULL? – Anilkumar Apr 04 '13 at 14:06
@Anilkumar - `ISNULL` is T-SQL dialect, is limited to only two inputs, and forces the type of the result to be the same as the type of its first input, no matter what the type precedence rules would normally say. `COALESCE` is standard SQL, extends to multiple inputs (returning the first non-NULL value) and performs normal type precedence to determine the result type. Outside of a few edge cases (not present here), I'd always choose `COALESCE`. – Damien_The_Unbeliever Apr 04 '13 at 14:09
@Anilkumar http://dba.stackexchange.com/questions/4274/performance-difference-for-coalesce-versus-isnull – Lamak Apr 04 '13 at 14:11

score 1 · Answer 3 · edited May 23 '17 at 12:06

SELECT CONCAT(Address1                                      -- concatenation of address 1 with..
           , CASE WHEN ISNULL(NULLIF(Address2, ''))         -- if address 22 is null or empty
             THEN ''                                        -- concat with nothing
             ELSE CONCAT( '  ,  ', Address2)                -- else concat with a comma and address 2
             END
       )

Sqlfiddle here

The line CASE WHEN ISNULL(NULLIF(Address2, '')) is for determine if address2 is null or empty. Details can be found here.

score 1 · Answer 4 · answered Apr 04 '13 at 14:02

1

If address2 can be either null or an empty string, or simply whitespace, you need something like:

select address1 + 
case when length(trim(ifnull(address2, ''))) > 0 then ', ' + address2
else '' end

answered Apr 04 '13 at 14:02

Dan Bracuk

20,699
4
26
43

how to check empty field in sql

4 Answers4