Doubt in LIKE Clause

Question

Post reply

Doubt in LIKE Clause

raghulvarma

SSC Rookie

Points: 32
More actions
April 1, 2010 at 4:22 am

#217365

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?

Viewing 3 posts - 1 through 2 (of 2 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Wildcards are expanded only for the right operand of the LIKE operator.

It works exactly the same in SQL Server, it's not Oracle specific:

DECLARE @TABLE TABLE (

COL1 VARCHAR(10),

COL2 VARCHAR(10),

COL3 VARCHAR(10)

)

INSERT INTO @TABLE VALUES('A','AA','AAA')

INSERT INTO @TABLE VALUES('B','BB','BBB')

INSERT INTO @TABLE VALUES('C','CC','CCC')

SELECT * FROM @TABLE WHERE COL1 LIKE '%'

SELECT * FROM @TABLE WHERE '%' LIKE COL1

SELECT * FROM @TABLE WHERE COL1 LIKE 'A'

SELECT * FROM @TABLE WHERE 'A' LIKE COL1

If you want to build a pattern on a column you can do the following:

SELECT * FROM @TABLE WHERE COL2 LIKE COL1 + '%'

Hope this helps

Gianluca

-- Gianluca Sartori

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

Oracle documentation is very specific about syntax when using LIKE operator...

search_value LIKE pattern

where:

search_value is usually a column name you want to search for a pattern.

pattern is what you want to find in search_value.

Pattern-matching characters like '_' and '%' are only allowed on pattern, is you include them on search_value they will be assumed to be a literal rather than a pattern-matching character.

Hope this helps.

_____________________________________
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.