CODEWITHSUNDEEP
sqlsql-server
4thSpace
4thSpace

Reputation: 44352

How to evaluate empty string and value without compare?

@myvar is an nvarchar(30). How can I do the following evaluation without using an OR?

@myvar = '' OR (table1.col1 like @myvar)

Is there some other way that is more efficient?

Upvotes: 0

Views: 62

Answers (4)

HABO
HABO

Reputation: 15852

If one had an interview question where OR was forbidden and De Morgan's laws were forgotten then one might use:

case
  when @myvar = '' then 42
  when table1.col1 like @myvar then 666
  else 0 end > 0

One might also experiment with Len( @myvar ) = 0.

Upvotes: 1

d89761
d89761

Reputation: 1434

table1.col1 like ISNULL(NULLIF(@myvar, ''), table1.col1)

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

There are other ways, in fact there are many. However, the form you have performs the best for most versions of SQL Server 2005 onwards if you add OPTION (RECOMPILE).

Look here for the definitive guide to searching with dynamic conditions. Follow the link for your version of SQL Server.

Upvotes: 1

Majid Laissi
Majid Laissi

Reputation: 19788

How about:

(table1.col1 like ISNULL(@myvar,table1.col1))

Upvotes: 1

Related Questions