CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
imran_ku07
imran_ku07

Reputation: 1404

Is this possible to use Like with ALL keyword

Let's say I have the following,

declare @A table (a int)

insert into @A
select 1 union all
select 2 union all
select 3

select a FROM @A 
where a > ALL (select 1 union all select 2)

This will work. But I need to use Like instead of greater than here,

declare @A table (a int)

insert into @A
select 1 union all
select 2 union all
select 3

select a FROM @A 
where a LIKE ALL (select 1 union all select 2)

Please help me.

Edit: Here is the my original old table look like,

declare @A table (a varchar(500))

insert into @A
select 'a;b;c' union all
select 'a;d;b' union all
select 'c;a;e'

My application is sending values in my SP as 'a;b' or 'b;c;a' etc,

Now I need to Select only those Rows in table @A which have a and b or b and c and a. I am using split function in my SP to make user input as table. That's why I need to use Like ALL here. But any other suggestion is also welcome.

Upvotes: 3

Views: 1251

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

It is not possible to use all with like. If I understand you correctly you could use something like this instead.

declare @T table (ID int primary key, Col varchar(25))

insert into @T
select 1, 'a;b;c' union all
select 2, 'a;b;c' union all
select 3, 'a;d;b' union all
select 4, 'bb;a;e'

declare @Input table(val varchar(10))
insert into @Input values('a')
insert into @Input values('b')

select T.ID
from @T as T
  inner join @Input as I
    on ';'+T.Col+';' like '%;'+I.val+';%'
group by T.ID    
having count(t.ID) = (select count(*) from @Input)

Result

ID
1
2
3

Upvotes: 2

Wicked Coder
Wicked Coder

Reputation: 1118

Try to create a new table which will have search values and then join it by using LIKE statement.

create table searchvalues

(

myvalues varchar(30)

)

INSERT INTO SearchValues    
SELECT '%1%'   
UNION SELECT '%2%'


SELECT * FROM @A    
join searchvalues on a like myvalues

Upvotes: 0

p.campbell
p.campbell

Reputation: 100607

Did you want an IN?

 SELECT a FROM @A WHERE a IN (1,2)

Upvotes: 0

Related Questions