Query using the LIKE OPERATOR

Question

Post reply

Query using the LIKE OPERATOR

ronaldkrex

Right there with Babe

Points: 768
More actions
March 24, 2010 at 11:14 am

#213103

I have been running the query below. For some reason I can type a value for name that I know is in the Name Field and I don't get any results. Any help is greatly appreciated.
SELECT * FROM [dbo].[tblComplimentsandComplaints]
WHERE (Name LIKE @Name or @Name IS NULL )
AND (Date >= @StartDate OR @StartDate IS NULL)
AND (Date < dateadd(dd,1,@EndDate) OR @EndDate IS NULL)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

upper(Name) LIKE upper(@Name) or @Name IS NULL

also remember to add "%" ... like in: upper(name) like upper('Jerem%')

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ronaldkrex Right there with Babe Points: 768 More actions · Answer 2

ronaldkrex

Right there with Babe

Points: 768

March 24, 2010 at 11:23 am

#1139094

What do you mean by Upper?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

ronaldkrex (3/24/2010)
What do you mean by Upper?

I mean... use upper() function on both ends of the condition as shows in previous post.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95460 More actions · Answer 4

PaulB-TheOneAndOnly (3/24/2010)
upper(Name) LIKE upper(@Name) or @Name IS NULL
also remember to add "%" ... like in: upper(name) like upper('Jerem%')

Using a function on the field being searched is going to negate any abiliity of index usage.

Upper is also only needed for case-sensitive collations.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 5

ronaldkrex (3/24/2010)
I have been running the query below. For some reason I can type a value for name that I know is in the Name Field and I don't get any results. Any help is greatly appreciated.
SELECT * FROM [dbo].[tblComplimentsandComplaints]
WHERE (Name LIKE @Name or @Name IS NULL )
AND (Date >= @StartDate OR @StartDate IS NULL)
AND (Date < dateadd(dd,1,@EndDate) OR @EndDate IS NULL)

If @Name is in the table, then you must be filtering out with @StartDate and/or @EndDate.

Do you get the record back if you run this:

SELECT * FROM [dbo].[tblComplimentsandComplaints]

WHERE Name = @Name

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

How about ?

select ....

from...

WHERE NAME LIKE isnull(@NAME,'')+'%'

Also, how about showing us the name you are trying to search, and the string that is in the @name variable. Cut and paste both please. Don't type from memory.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 7

ronaldkrex (3/24/2010)
I have been running the query below. For some reason I can type a value for name that I know is in the Name Field and I don't get any results. Any help is greatly appreciated.
SELECT * FROM [dbo].[tblComplimentsandComplaints]
WHERE (Name LIKE @Name or @Name IS NULL )
AND (Date >= @StartDate OR @StartDate IS NULL)
AND (Date < dateadd(dd,1,@EndDate) OR @EndDate IS NULL)

Did you try using TRIM functions such as LTRIM or RTRIM and recommend your @name variable being one of these types

@Name = '%name' OR @Name = '%name%' OR @Name = 'name%'

SELECT * FROM [dbo].[tblComplimentsandComplaints]

WHERE (RTRIM(LTRIM(Name)) LIKE @Name or @Name IS NULL )

AND (Date >= @StartDate OR @StartDate IS NULL)

AND (Date < dateadd(dd,1,@EndDate) OR @EndDate IS NULL)

Also as "The Dixie Flatline" suggests Cut and paste the name you are trying to search, and the string that is in the @name variable