Like statement in sql - 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 2008 Forums

Other SQL Server 2008 Topics

Like statement in sql

Author

Topic

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-28 : 09:55:10

how can i get all records starting with '%' using like statement ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-28 : 10:13:16

quote:
Originally posted by shobinmathew

how can i get all records starting with '%' using like statement ?

Select * from your_Table
where col like '[%]%'

Madhivanan

Failing to plan is Planning to fail

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-29 : 02:43:31

I have a input textbox in my web application. Based on the value entered in it I have to get the result if I am entering %123 i should get all records starting with '%123'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-29 : 03:48:35

quote:
Originally posted by shobinmathew

I have a input textbox in my web application. Based on the value entered in it I have to get the result if I am entering %123 i should get all records starting with '%123'

select data from
(
select '7876' as data union all
select '%123' union all
select '123'
) as t
where data like '[%]123'

Madhivanan

Failing to plan is Planning to fail

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-29 : 04:50:29

123 i gave as an example.
I want to know how we can do this whils passing as a variable.
eg:
select
name
from
customers
where
name
like
@ShopperName + '%'
--------------------------
If I paas to the store proc '%123' as the value for shoppername it will return all values in the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-29 : 06:57:36

quote:
Originally posted by shobinmathew

123 i gave as an example.
I want to know how we can do this whils passing as a variable.
eg:
select
name
from
customers
where
name
like
@ShopperName + '%'
--------------------------
If I paas to the store proc '%123' as the value for shoppername it will return all values in the table

declare @v varchar(100)
set @v='%123'

select data from
(
select '7876' as data union all
select '%123' union all
select '123'
) as t
where data like replace(@v,'%','[%]')+'%'

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-29 : 07:04:34

Or make use of ESCAPE clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

shobinmathew
Starting Member

16 Posts

Posted - 2008-06-16 : 07:15:24

can u please explain what is ESCAPE clause.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-16 : 07:20:57

As per SQL Server books online:

quote:
Use the ESCAPE keyword to define an escape character. When the escape character is placed in front of the wildcard in the pattern, the wildcard is interpreted as a character. For example, to search for the string 5% anywhere in a string, use:
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources