LIKE fails with exact matches - 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)

LIKE fails with exact matches

Author

Topic

Guran
Starting Member

17 Posts

Posted - 2005-08-26 : 04:21:43

In my sproc I use LIKE for some searches among members in my system. The problem is I allow NULL for some of the fields so I decided to use this SELECT statement for the search:

SELECT * FROM Members WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName AND (PersNr LIKE @PersNr OR PersNR IS NULL) AND (BirthYear LIKE @BirthYear OR BirthYear IS NULL) ORDER BY LastName ASC

Basically I want to return results that are NULL aswell (there are only a few of them) even if they dont actually match the search. The problem if I enter a PersNr or a BirthYear that is an exact match to a record in the database that record is not returned when using the OR BirthYear IS NULL part. For example if I enter 1995 as the BirthYear which is an exact match to some members birthyear the search only returns those members that have NULL as birthyear. If I enter 995 as birthyear it works fine and returns an expected result.

Could anyone please explain this behaviour to me?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 04:23:38

When you use Like operator you have to use '%'+@FirstName+'%'
Post the table structure, sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Guran
Starting Member

17 Posts

Posted - 2005-08-26 : 05:07:59

The wildcards are added in the code for the application using this sproc. I have tested it and it works for the first and last name so the wildcards work. With the names I can enter an exact search and get correct results.

The table structure is this:

CREATE TABLE Members
(
	MemberID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
	FirstName varchar(64) NOT NULL,
	LastName varchar(64) NOT NULL,
	PersNr varchar(10),
	OriginalClub varchar(128),
	SeasonNr varchar(4),
	BirthYear varchar(4)
)

Lets say I have these records (only including the parts that are searchable (the columns are here first name, last name, PersNr and birthyear):

John Doe NULL NULL
Spider Man 951004 1995
Bat Man 821223 1982

Lets say I want to search for all members whose birthyear is 1995. I enter 1995 in the birthyear field and leave the other fields blank.

The result I get is:
John Doe NULL NULL

The result I want is:
John Doe NULL NULL
Spider Man 951004 1995

If I enter 995 in the birthyear search field I get the above result but when the search is exact it fails for some reason, and it only does this for the fields that allow nulls.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 05:18:36

Try this

Select first name, last name, PersNr , birthyear from Members
where birthyear like '%1995%' or birthyear is Null

Madhivanan

Failing to plan is Planning to fail

Guran
Starting Member

17 Posts

Posted - 2005-08-26 : 05:24:17

Yes that worked like as expected. What changes do you mean I should do to my code?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 05:32:13

Try this

SELECT * FROM Members WHERE FirstName LIKE '%'+@FirstName+'%' AND LastName LIKE '%'+@LastName+'%' AND
(PersNr LIKE '%'+@PersNr+'%' OR PersNR IS NULL) AND (BirthYear LIKE '%'+@BirthYear+'%' OR BirthYear IS NULL)
ORDER BY LastName ASC

Madhivanan

Failing to plan is Planning to fail

Guran
Starting Member

17 Posts

Posted - 2005-08-26 : 05:52:31

Yes that worked. So it seems it's better to have the wildcards in the SQL rather than in my program code.

Thanks a lot for your extremely quick replies!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 06:00:13

Yes It is

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-26 : 08:04:35

you can also get rid of the or conditions if you use isnull or coalesce:

if you want to catch nulls as matchs:
SELECT *
FROM Members
WHERE FirstName LIKE '%'+@FirstName+'%'
AND LastName LIKE '%'+@LastName+'%'
AND IsNull(PersNr,@PersNr) LIKE '%'+@PersNr+'%'
AND IsNull(BirthYear,@BirthYear) LIKE '%'+@BirthYear+'%'
ORDER BY LastName ASC

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-26 : 09:00:14

You have a fundamental flaw in your logic -- it appears you are writing this query so that if the parameter @BirthYear is null, then ignore the birth year column in the table, but if @BirthYear is NOT null, then filter based on the column BirthYear = @Birthyear.

That's fine, but the problem is what you mentioned: sometimes the BirthYear column is Null for rows in the table.

That leads to the question, then: what should the parameter @Birthyear be set to if I want to see the rows where BirthYear is null?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources