Solved

Using LIKE and OR to look for more then one pattern in a field

Posted on 2009-04-03
2
209 Views
Last Modified: 2013-11-28
Hi all this is probably pretty simple but I dont work in SQL but 4gl

I have a sql statement that uses like to find entries that contain the term 'out' for a particular date range.

What I want to do is specify that I want to look for entries that contain the word 'out' OR 'in' OR 'pto'.

Looking at the code example below how would I change this with the following select statement.
Original working query:

 

query = "SELECT * FROM cal_events E INNER JOIN cal_dates D" _

	& " ON (E.event_id = D.event_id) " _

	& " WHERE (event_title LIKE '%out%') AND (event_date BETWEEN #" & Date & "# AND #" & Date & "#) AND (user_id = " _

	& Session(dataName & "User") & " OR " _

	& "private = 0) ORDER BY event_date"

 

 

I tried changing to:

 

query = "SELECT * FROM cal_events E INNER JOIN cal_dates D" _

	& " ON (E.event_id = D.event_id) " _

	& " WHERE (event_title LIKE '%out%') OR " _

	& "(event_title LIKE '%in%') & OR " _

	& "(event_title LIKE '%pto%') & AND " _ 

	& "(event_date BETWEEN #" & Date & "# AND #" & Date & "#) AND (user_id = " _

	& Session(dataName & "User") & " OR " _

	& "private = 0) ORDER BY event_date"

	

OR can I use a wild card to simplify something like:

 

query = "SELECT * FROM cal_events E INNER JOIN cal_dates D" _

	& " ON (E.event_id = D.event_id) " _

	& " WHERE (event_title LIKE '[out,in,pto]%') AND (event_date BETWEEN #" & Date & "# AND #" & Date & "#) AND (user_id = " _

	& Session(dataName & "User") & " OR " _

	& "private = 0) ORDER BY event_date"

Open in new window

0
Comment
Question by:binovpd
2 Comments
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 50 total points
ID: 24063034
The first one you changed to is correct. Once cannot use as OR operator in a group for LIKE operator the way you do for IN operator. Since your words are part of bigger words/phrases/sentences you need to use LIKE and each LIKE separated by OR.
0
 

Author Closing Comment

by:binovpd
ID: 31566378
Thanks k_murli_krishna I got it to work.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now