CODEWITHSUNDEEP
sql-serverselectnull
hotcoder
hotcoder

Reputation: 3256

select query, compare null records

I'm writing an sql server query:

select * from vwApplicationList
            where status='Unassigned' AND
            BBL like '%' + @BBL + '%' AND
            UnitOrAppartmentNumber like '%'+@Appartment+'%' AND
            ResidenceTypeDescription like '%'+@ResidenceTypeDescription+'%' AND
            SN1 like '%'+@SN1+'%' AND
            SN2 like '%'+@SN2+'%'

The problem is that the field "SN2" is null in several records. So how can I compare them?

Upvotes: 0

Views: 162

Answers (3)

dov.amir
dov.amir

Reputation: 11627

you can do 

COALESCE(SN2 ,'') like '%'+@SN2+'%'

COALESCE

Upvotes: 3

cadrell0
cadrell0

Reputation: 17337

NULL and only be compared with IS NOT and IS NOT NULL.

If you want to always include NULL you could try

(SN2 like '%'+@SN2+'%' OR SN2 IS NULL)

If you only want to include NULL when @SN2 is empty, try something like

(SN2 like '%'+@SN2+'%' OR (@SN2 = '' AND SN2 IS NULL))

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

If you want a positive result on the comparison:

... AND COALESCE(SN2, @SN2) LIKE '%' + @SN2 + '%'

If you want a negative result (assuming @SN2 is not also an empty string):

... AND COALESCE(SN2, '') LIKE '%' + @SN2 + '%'

Upvotes: 2

Related Questions