Doubt in LIKE Clause - 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

General SQL Server Forums

New to SQL Server Programming

Doubt in LIKE Clause

Author

Topic

raghulvarma
Starting Member

21 Posts

Posted - 2010-04-01 : 06:21:41

using Oracle
A table has 3 rows and 3 cols, Here col1,col2,col3 are all varchar
Col1 Col2 Col3
A AA AAA
B BB BBB
C CC CCC

Now I write a select Query
Select * from table where col1 like '%'
it returns all the rows

Now i put another query

Select * from table where '%' like col1
it does not return any values

but when I change the query as
Select * from table where col1 like 'A'
it returns the first row

Now i put another query

Select * from table where 'A' like col1
it returns the first row

Now the question is if I give the record value ("A") it returns correct values but if i
give a wild card character it does not return the value WHY?

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:28:17

"Select * from table where '%' like col1
it does not return any values"

I think you meant to type


Select * from table where col1 like '%'

In your version SQL is correcting saying that "%" is not like "A", nor like "B" nor like "C"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 06:30:33

The wild card character should be on the right hand side on the left side of the "LIKE".

Putting it on the left side does not perform any LIKE matching at all and hence yield no result.

Not sure how does this behave in Oracle but at least it is in SQL Server.

Incase you didn't notice, you have posted in New to SQL Server programming

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 06:38:29

Because as per LIKE syntax pattern is specified on the right hand side of the operator. 'A' like Col1 works because it is not pattern matching, but string matching.

Harsh Athalye
http://www.letsgeek.net/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 06:40:08

<<
Not sure how does this behave in Oracle but at least it is in SQL Server.
>>

It works same way as how it works in SQL Server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources