SQLTeam.com | Weblogs | Forums

How to check isnull or empty in nvarchar column

sql2008
sql2008r2

#1

Hi

How to check for isnull or empty? I'm trying to check as below but its not returning expected results.

AND (CS.StudentName like '%'+ISNULL(@StudentName,CS.StudentName)+'%')
AND (Cl.Street like '%'+ISNULL(@StreetAddress,Cl.Street)+'%')

The above statements are ignoring the rows if the column has explicit NULL value.

Thanks in advance.
--Mohan


#2

where (CS.StudentName is null or CS.StudentName = '' )


#3

Thanks for your reply Muj9.

But I need to search basing on the input param if user passes either @StreetAddress or @Studentname or both.


#4

If you are trying to check for strict equality, do the following:

AND 
	(
		CS.StudentName = @StudentName 
		OR NULLIF(@StudentName,'') IS NULL
	)
AND
	(
		Cl.Street = @StreetAddress 
		OR NULLIF(@StreetAddress,'') IS NULL
	)

If you are trying to do a LIKE condition, then this:

AND 
	(
		CS.StudentName LIKE '%'+@StudentName+'%'
		OR NULLIF(@StudentName,'') IS NULL
	)
AND
	(
		Cl.Street LIKE '%'+@StreetAddress+'%'
		OR NULLIF(@StreetAddress,'') IS NULL
	)

I have to caution you though, that this type of "catch-all" queries tend to perform poorly. Take a look at this blog.


#5
AND (@StudentName IS NULL OR CS.StudentName like '%'+@StudentName+'%')	
AND (@StreetAddress IS NULL OR Cl.Street like '%'+@StreetAddress+'%')