3 replies [Last post]
sanch3x
Offline
Enthusiast
Last seen: 10 years 45 weeks ago
Timezone: GMT-4
Joined: 2005-05-27
Posts: 223
Points: 2

Good mornin' to those on my side of the planet Smile,

I have two questions regarding SQL queries that I can't seem to figure out on my own.

Solved - 1. How do I make my query search through multiple fields? I thought you had to seperate them with commas but this seems to be giving me an error (I am on an oracle database).
"SELECT * FROM tbl WHERE field1, field2 LIKE '%keyword%'"
does not work.

it's not the OR it's the ||... working with ASP is making my brain rot.

2. I don't know if this is only the case with certain DB's or if it's a generic thing, but when I execute a search with the LIKE in my query the results I receive are not the same if the keywords are in a string or seperated in different ones. (whoa long sentence there)

For example:
"Canadian Dairy Commission"
and
Canadian Dairy Commission

won't return the same results because in the first case the SQL query looks like

SELECT * FROM tbl WHERE field LIKE '%Canadian Dairy Commission%'

while the latter one looks like

SELECT * FROM tbl WHERE field LIKE '%Canadian% %Dairy% %Commission%'

Maybe my syntax is wrong but using SQLToad I do not get an error running the query, I just get no results.

Thanks for any help Smile

Seb

"Don't worry about Blank let me worry about Blank"

Tags:
briski
briski's picture
Offline
Elder
London
Last seen: 8 years 9 weeks ago
London
Timezone: GMT+1
Joined: 2004-02-16
Posts: 1066
Points: 0

A questions about SQL queries

Not an expert here in SQL but the % is a wild card character that can be replaced by anything or nothing.

So

SELECT * FROM tbl WHERE field LIKE '%Canadian Dairy Commission%'

Will return anything that contains "Canadian Dairy Commission" as a full string.

The whole string may have stuff before and after it like
"It's a great Canadian Dairy Commission we have here"

or just after
"Canadian Dairy Commission closes down due to milk shortage"

Or just before
"Lets close the Canadian Dairy Commission"

but not things like

"The Canadian's and their pesky Dairy Commission!!"

However the second one should return all of these examples as the % means that between each word you can have any number of things.

I have no idea if that makes sense and I am prepared to be corrected here. As I said I am no expert on SQL but that's my understanding.

sanch3x
Offline
Enthusiast
Last seen: 10 years 45 weeks ago
Timezone: GMT-4
Joined: 2005-05-27
Posts: 223
Points: 2

A questions about SQL queries

Hi, thanks for the reply

I was under the same impression as you, and it might be right. However wouldn't the second search find the same results as the one wrapped in quotation marks?

If wrapped I get 51 results and if they aren't I get zip. Is there a way to insert an OR in there somewhere?

Seb

"Don't worry about Blank let me worry about Blank"

Chris..S
Chris..S's picture
Offline
Moderator
Last seen: 7 years 46 weeks ago
Timezone: GMT+1
Joined: 2005-02-22
Posts: 6078
Points: 173

A questions about SQL queries

I don't know Oracle. But if it was in MySQL I would need to do something like.

SELECT * FROM tbl WHERE field1 LIKE 'Canadian Dairy Commission' OR field2 LIKE 'Canadian Dairy Commission';

MySQL has something called a "Full Text Search function" which uses MATCH ... AGAINST ... I would guess Oracle has something similar.