How to Use LIKE with NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to Use LIKE with NULL

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-08-04 : 07:30:22

I am selecting columnA and columnB while I am using LIKE on columnB. The query returns only rows where columnB is not NULL. But I want the query to return all rows even where columnB is NULL. Please tell me how to use LIKE in this scenario.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 07:41:26

Select columns from yourTable where ColumnB like '%somevalue%' or columnB is null

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-08-04 : 07:48:39

I had done this, but in this case when I pass some valid value like 'P%' then it displays all records starting with P and NULL as well. But this time I need only rows starting with P

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-04 : 08:15:07

eh ?

you have given absolutely opposite conditions

>>But I want the query to return all rows even where columnB is NULL.

and

>>But this time I need only rows starting with P

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 08:42:36

As said, your original question is wrong
Do you need something like this?

If exists(Select * from yourTable where ColumnB like '%somevalue%')
Select columns from yourTable where ColumnB like '%somevalue%' 
else 
Select columns from yourTable where columnB is null

Madhivanan

Failing to plan is Planning to fail

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-04 : 10:47:07

WHERE ISNULL(tblPerson.txtNameLast, '') LIKE CASE WHEN @txtPersonNameLast IS NOT NULL THEN @txtPersonNameLast
ELSE ISNULL(tblPerson.txtNameLast , '')
END

Might give you something close.

Aj

--What happens when social workers and programmers collide:
Social Worker: "They aren't an Identified Resource Family, they are an IR Family."
Me: "What does IR stand for?"
Social Worker: "Identified Resource."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources