Mulitple LIKES
-
hello all,
Question - does any body know whether the following can be avoided:
we need to select the columns where column_values is like an answer in a table but we need to use mulitple like statements.. but is there any way to avoid the followingselect * from table_name where column_name like 'aa%' and column_name like 'bb%' and column_name like 'ccc%' etc etc ...grateful for any answers ....Thanks
-
If you always know the first few letters, you could try left(column,2) = 'aa' instead of like 'aa%'. An index on the column that you are searching will also help in this instance.
If you have a table that contains the search items, eg
ID SearchItem
1 aa
2 bb
etc
you can use this in a WHERE clause
WHERE left(column, 2) in (select SearchItem from SearchTable)
Hope that makes some sense!
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thanks Phil !!! it makes sense .. (and wish I could come up with that ....:wow
-
Am I missing something ?
WHERE left(column, 2) in (select SearchItem from SearchTable)
Will return true when at least one of the search items is found!!
but the construct:
colum_name Like searchpattern and column_name like searchpattern ...
implies that ALL searchpatterns must be found!
which is not the same
* Noel -
The condition should probably be:
WHERE left(column, 2) ALL (select SearchItem from SearchTable)
but then again is not a pattern match is an exact match
* Noel -
a possible solution to pattern search may be this one:
select a.id
from
Answers a cross join searchconditions s
group by a.id
having sum( case when a.text like s.search +'%' tehn 1 else 0 end)
=
(select count(*) from searchconditions)
* Noel -
I feel sure Lauren meant to write
select * from table_name where column_name like 'aa%' OR
column_name like 'bb%' OR
column_name like 'ccc%' etc etc
"column_name like 'aa%' and column_name like 'bb%'" would never return anything, because the first characters cannot be 'aa' and 'bb'
-
The real problem maybe is that we are guessing !!
I can also interpret that: each of the "column_name" is a different column
* Noel -
Agree - the original query will return nothing...
-
Hi all I did mean
Select * from table_name where column_name like 'aa%' OR column_name like 'bb%'....
A further resolution to the query was :
select count(user_id), user_id from dbo.vpc_feedback_2
where ((question_id=1
and feedback like 'Up%')
or (question_id=2
and feedback like '95%%')
or (question_id=3
and feedback like '%Japan%')
or (question_id=4
and feedback like 'Zwartkops%')
or (question_id=5
and feedback like '%bike%'))
and user_id in (
select user_id from dbo.vpc_feedback_2
group by user_id
having count(user_id) = 5
)
group by user_id
having count(user_id) = 5
The table was being used to store answers to a competition and the above sql statement was to return the winners .......
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply