Like % question Criteria

[camilo lopes]

Hi Guys,

I am confused with LIKE in Criteria I have passed this value to my string: "lop%es"

and in the SQL Server i have saved : lopes (column last name )

the result is true. why? I expected that my criteria list would be empty.

criteria.add(Restrictions.like("lastName", "lop%es"));

any idea guys?

thanks.

[Vijitha Kumara]

What happens if you execute a SELECT statement against the table with ...lastname LIKE 'lop%es' ? Wouldn't it return any result?

[camilo lopes]

Hi Kamara,

I executed in DB and the result was empty. so, in DB worked as expected. I did not understand, because in hibernate is different. do you have any idea?

[Vijitha Kumara]

"%" act as a wild card character so you should not get an empty result. What query did you run?

[camilo lopes]

I executed

select * from user where lastname LIKE 'Lop%es"

in my db saved we have:
name: camilo
lastname: Lopes

[Vijitha Kumara]

Why you use single and double quotes in the query? Use only single quote. And can you show a screen of this if it still returns no results?

[camilo lopes]

oh sorry I wrote wrong. this is correct:

select * from user where lastname LIKE 'Lop%es'

the result is empty.

[Gamini Sirisena]

If do
select * from user where lastname='Lopes'
what happens?

[camilo lopes]

hm of this way it works.

select * from user where lastname='Lopes' 

I do not why of this. I executed againt hsqldb via Hibernate using criteria Like and the string 'LoP%es' the result was as expected. any result. But with sql server, I have data returned with 'Lop%es' what should be ignored once that I do not have this data in DB.

it is strange, is not it?

[Vijitha Kumara]

What happens for any other combination, LIKE 'Lopes%' or LIKE '%Lopes%' etc...?

[camilo lopes]

of this way, it works as expected. db return the record. I executed:

LIKE '%Lopes%' 

LIKE '%Lopes' 

LIKE 'Lopes%' 

and

LIKE '%Lo%pes%' : the problem here, the DB returned data. I expected empty.

do you get to see any problem?

[Vijitha Kumara]

They all seems fine then. Have you used any proprietary type for the column?