CODEWITHSUNDEEP
mysqldesign-patternswildcardsql-like
Sadegh
Sadegh

Reputation: 6854

How to write a query when pattern is in field

I've got a table in which a field contains pattern like 'hey *' or '%test%'. Like this :

Id - f_pattern - f_Respond
1  - 'hey *' - 'hello there'
2  - 'how are you *' - 'am fine'

Is is possible that i write query like this :

select * from table where f_pattern like 'hey bobby'

and it returns the first row ?

Upvotes: 1

Views: 727

Answers (2)

Bohemian
Bohemian

Reputation: 424973

Yes, if you change your patterns to "like compatible" values, and reverse the like logic:

select * from pattern_table
where 'hey bobby' like replace(f_pattern, '*', '%')

Upvotes: 4

Mariusz Sakowski
Mariusz Sakowski

Reputation: 3282

have you tried

select * from `table` where field like 'hey %'

//edit

it surprised me, but this works:

create database test;    
use test;
create table pattern (a varchar (100));
insert into pattern values ('a%');
create table subject (a varchar (100));    
insert into subject values ('abc'), ('cde');
select * from subject where a like (select a from pattern limit 1);

-- result
-- +------+
-- | a    |
-- +------+
-- | abc  |
-- +------+
-- 1 row in set (0.03 sec)

see: http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

Upvotes: 1

Related Questions