Sql query for like operator

Question

I have a search box where user enters anything in textbox and submits.Lets say user enters Peter.If we are comparing in sql table with following query

select * from xml where name like '%peter%'

This query works well,but when i want to search for only peter in the string,then it creates a problem.Like when i want to search for only those rows which are having Peter present in the name column. in name column,value 1-My name is Peter. valu2-My name is Peterwatter. valu3-My name is Peter living in US. when i want to search it should give only first and third row.not the second one.BUt with above query i m getting all the three rows.How can i correct that.Please guide on this

@Mihai why wouldn't this return the second one?

Hituptony
– Hituptony

2014-04-28 17:11:39 +00:00
Commented Apr 28, 2014 at 17:11 — Hituptony
– Hituptony, Commented Apr 28, 2014 at 17:11

Ravinder Reddy · Accepted Answer · 2014-04-28 17:07:33Z

3

select * from xml 
 where name like '%peter'
    or name like '%peter %'

answered Apr 28, 2014 at 17:07

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

uncoder · Accepted Answer · 2014-04-28 17:08:36Z

1

There is a way to do so with regular expressions, but a simple solutions can be like this:

[name] like '% peter %' 
or [name] like '% peter' 
or [name] like 'peter %' 
or [name] = 'peter'

answered Apr 28, 2014 at 17:08

uncoder

1,9161 gold badge17 silver badges20 bronze badges

Comments

b3ko · Accepted Answer · 2014-04-28 17:08:26Z

0

try like '% peter %' (with spaces after/before the wild cards) However please watch out for sql injections. http://en.wikipedia.org/wiki/SQL_injection

answered Apr 28, 2014 at 17:08

b3ko

3902 silver badges7 bronze badges

12 Comments

Hituptony Over a year ago

Would this not select all three values? Are you sure?

user3575612 Over a year ago

will this b prone to sql injection attacks sir.if we dont escape characters sir.

b3ko Over a year ago

@Hituptony actually i have a mistake and it will only select row 3 since there is not a space after peter. Seems that coder's answer above is better.

b3ko Over a year ago

@user3575612 this type of query is prone to sql injection because you are taking user input and passing it to the database. perhaps you can paramatize the query and turn it into a stored procedure.

Hituptony Over a year ago

There is a period after 'My name is Peter.'

|

Syed Mauze Rehan · Accepted Answer · 2014-04-29 03:35:55Z

0

select * from xml 
 or name like '%Peter.'
 or name like 'Peter%'
 or name like 'Peter %'
 or name like '% Peter.'
 or name like '% Peter %'

Edit:: Now it will.

edited Apr 29, 2014 at 3:35

answered Apr 28, 2014 at 17:44

Syed Mauze Rehan

1,1451 gold badge16 silver badges32 bronze badges

1 Comment

Hituptony Over a year ago

Will this exclude the second value? 1-My name is Peter. valu2-My name is Peterwatter. valu3-My name

Collectives™ on Stack Overflow

Sql query for like operator

4 Answers 4

Comments

Comments

12 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related