0

Possible Duplicate:
Using SQL LIKE and IN together

I have table as below.

create table tab1 (
    id int, col1 varchar(20), col2 varchar(20),
    col3 varchar(20), col4 varchar(20)
);

I have values inside it as 

insert into tab1 values
(1,'column011','column012','column013','column014'),
(2,'column021','column022','column023','column024'),
(3,'column031','column032','column033','column034'),
(4,'column041','column042','column043','column044')
;

Earlier I had requirement as, Find row, where any column HAVE data as column011. So I executed below query and got what I was desired.

select * from tab1
WHERE 'column011' in (col1,col2,col3,col4);

I have used above against CONCAT_WS as it was simple query.

select * from tab1 
where CONCAT_WS('-', col1, col2, col3, col4) LIKE '%column011%';

Now I got requirement as, Find row, where any column CONTAIN data as mn01. So I executed below query and got what I was desired.

select * from tab1 
where CONCAT_WS('-', col1, col2, col3, col4) LIKE '%mn01%';

However I was thinking to get this done by using simple query (first query). So I tried with below.

select * from tab1
WHERE '%mn01%' in (col1,col2,col3,col4);

Is there any way where I can do what I want by modifying above query? (Last query)

Data at sqlfiddle

Community
  • 1
  • 1
Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
  • Possibly because there are a hundreds of duplicates on this exact question covering every major RDBMS? – Ben Dec 26 '12 at 16:13
  • @Ben : The link you provided doesn't looks DUPLICATE to me. Can you provide me few more duplicates? – Fahim Parkar Dec 26 '12 at 16:17

1 Answers1

0

How about a variable?

set @var = 'mn01';
select * from tab1
WHERE '%' & @var & '%' in (col1,col2,col3,col4);
bonCodigo
  • 14,268
  • 1
  • 48
  • 91