Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a query for searching records like:
SQL
SELECT * FROM table WHERE column LIKE '%value%';


It's working fine but i want to put the reverse searching also like:
SQL
SELECT * FROM table WHERE value LIKE column;


Which becomes on combining like.
SQL
SELECT * FROM table WHERE column LIKE '%value%' OR value LIKE column;


Suppose column has a value like 123456 and i want to partially match it with 0123456. So i need a query to do this operation as:
SQL
SELECT * FROM table WHERE column LIKE '%0123456%' OR 0123456 LIKE column;


So what is the correct syntax for this kind of query.
Posted
Updated 27-Nov-14 21:05pm
v4

check this
SQL
SELECT * FROM table WHERE charindex(column,'0123456')>0 OR charindex('0123456',column)>0


Use INSTR for MySQL, Note It works in reverse order of charindex.

check this
mysql-instr-function[^]
 
Share this answer
 
v2
Comments
Shweta N Mishra 28-Nov-14 5:11am    
Ok, Try INSTR
Manudhiman 28-Nov-14 5:13am    
Already tried that. In MySQL we Use INSTR instead of CHARINDEX. And INSTR('0123456',column)>0 returns EMTPY SET.
Shweta N Mishra 28-Nov-14 5:14am    
And INSTR(column,'0123456') ?
Manudhiman 28-Nov-14 5:16am    
INSTR(column,'0123456') returns empty set because table has '123456' not '0123456'. And INSTR(column,'123456') returns the record.
Shweta N Mishra 28-Nov-14 5:17am    
No, its not the way it should work, i do not have enviornment to check.

your column contains 123456 and string you are looking for is 0123456 then
INSTR('0123456',column) or in reverse case it should work.
Assuming SQL-Server.

Something like this maybe:
SQL
SELECT  *
FROM    table
WHERE   column LIKE '%' + @Value + '%'
    OR  @Value LIKE '%' + column + '%'
 
Share this answer
 
v2
Comments
Manudhiman 28-Nov-14 3:23am    
No I've already tried it and getting empty set.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900