'%' operator in Like expression returns all rows

Question

4.50/5 (2 votes)

See more:

SQL-Server-2008

Hello

I have an SQL query which is used to fetch the user details based on firstname. i.e, for e.g if 'a' is given, the users containing 'a' in their firstname should be listed.Im using the '%' operator in my Like Expression. When im fetching using any string such as 'a', 'ss','123' etc,the result is perfect. But if im using '%' or '_' as the search text, then all the rows from table are retrieved instead of giving only those rows having '%' or '_' respectively in their firstname.

I want to get the result as follows
1. if search text is '%' then get only those rows which contains % in the firstname.
2. if search text is '_' then get only those rows which contains _ in the firstname.

Any help will be highly appreciated.

Regards
Jiju.

Posted 11-May-12 20:22pm

Jijutj

Add a Solution

5 solutions

Solution 3

Dear Friend, Check this website, May be this will be ur Exact solution.

http://www.w3schools.com/sql/sql_wildcards.asp[^]

Posted 11-May-12 21:12pm

Ranjith Reddy CSE

Comments

sravani.v 12-May-12 4:02am

5!

Solution 5

i can create a stored procedure and execute it
It will be like

SQL

create procedure getallelementa(@startletter char(1))
as
select * from<tablename> where left(firstname,1)in(@startletter)

Execution of this stored procedure will return you the details of all the customers whose name starts with the letter that you have entered for

Regards
Rakshith

Posted 14-Oct-13 23:35pm

Rakshith Kumar

Updated 14-Oct-13 23:38pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

P.Salini · Accepted Answer · 2012-05-11T20:28:00

Solution 1

You are getting all records for % and _ because those are wildcard characters in Like operator.
To know more go through this
http://msdn.microsoft.com/en-us/library/ms179859.aspx[^]

http://www.w3resource.com/sql/wildcards-like-operator/wildcards-like-operator.php[^]

Posted 11-May-12 20:28pm

P.Salini

Updated 11-May-12 20:30pm

v2

Comments

VJ Reddy 12-May-12 3:32am

Good references. 5!

P.Salini 12-May-12 3:33am

Thank you V J

Sandeep Mewara 12-May-12 3:34am

My 5!

P.Salini 12-May-12 3:56am

Thank you Sandeep

sravani.v 12-May-12 4:00am

5!

P.Salini 12-May-12 4:02am

Thank you Sravani

Maciej Los 12-May-12 4:01am

Good answer, my 5!

P.Salini 12-May-12 4:03am

Thank you losmac

hitech_s 12-May-12 4:12am

My 5!

Wendelius 12-May-12 4:46am

Exactly

P.Salini 12-May-12 4:58am

Thank you Wendelius

Shahin Khorshidnia 12-May-12 6:44am

My +5

P.Salini 12-May-12 7:19am

Thank you Shahin

Rockstar_ · Accepted Answer · 2012-05-11T20:37:00

Solution 2

Hi friend,

Keep % and _ in [] brackets defenitly it will come

Posted 11-May-12 20:37pm

Rockstar_

Comments

P.Salini 12-May-12 2:45am

My 5!

VJ Reddy 12-May-12 3:32am

Good answer. 5!

Maciej Los 12-May-12 4:01am

Good answer, my 5!

Jijutj 12-May-12 5:13am

It really solved my issue. Hearty thanks....

VJ Reddy · Accepted Answer · 2012-05-11T21:54:00

Solution 4

The solution 1 and 2 are very good and clearly give the answer for including % or _ literal in the search expression.

In case you want to enter the % _ in a normal way, for example from a TextBox from user input and then want to construct the filter expression, the following regular expression can be used to replace the occurances of % and _ with [%] and [_] respectively.

C#

string filterExpression = "a%b%_";
filterExpression = "%"+System.Text.RegularExpressions.Regex
                    .Replace(filterExpression,"([%_])","[$1]") + "%";

Console.WriteLine (filterExpression);
//Output
//%a[%]b[%][_]%

Posted 11-May-12 21:54pm

VJ Reddy

Comments

sravani.v 12-May-12 4:01am

5!

VJ Reddy 12-May-12 4:08am

Thank you, sravani.

Wendelius 12-May-12 4:47am

Good example

VJ Reddy 12-May-12 5:03am

Thank you, Mika.

Shahin Khorshidnia 12-May-12 6:44am

+5

VJ Reddy 12-May-12 6:57am

Thank you, Shahin.