String Search with SQL

[GH]

I was trying to write an SQL statment that will do a string search in an
Access 2000 database. But I can't seem to figure out how to do it so
that the search is case-INsensitive. Here's the SQL I have so far:

SELECT autoQuesID,fldQuesTitle,fldBody
FROM tblFAQ_Question
WHERE fldBody LIKE '%airline%';

All this will do is search for "airline" in lower case. So if the word
"Airline" is in the field I'm searching in...it won't find it. I've
also tried this SQL statment:

SELECT autoQuesID,fldQuesTitle,fldBody
FROM tblFAQ_Question
WHERE LCase(fldBody) LIKE '%airline%';

This won't return any rows, even if there is a match in the field. Can
anyone help me out?

Thanks!

[Bob Barrows [MVP]]

GH wrote:

> I was trying to write an SQL statment that will do a string search in
> an Access 2000 database. But I can't seem to figure out how to do it
> so that the search is case-INsensitive. Here's the SQL I have so far:
>
> SELECT autoQuesID,fldQuesTitle,fldBody
> FROM tblFAQ_Question
> WHERE fldBody LIKE '%airline%';
>
> All this will do is search for "airline" in lower case. So if the
> word "Airline" is in the field I'm searching in...it won't find it.

Not according to the documentation, or my tests.

This code:
<%
dim cn, rs, sSQL
set cn=createobject("adodb.connection")
sSQL="Select * From Table1 WHERE [Name] Like '%home%'"
cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("db7.mdb")
Set rs=cn.Execute (sSQL,,adCmdText)
Response.Write rs.GetString(adClipString,," | ","<BR>")
rs.Close:set rs=nothing
cn.Close:set cn=nothing
%>

produced this result:
9 | Anti-Homeopathy Illuminati | 6


According to the documentation, all searches are case-insensitive. This
statement:
Select * From Table1 WHERE [Name] = 'anti-homeopathy Illuminati'

produced the same result. It's case-sensitive searches that are hard to do.

> I've also tried this SQL statment:
>
> SELECT autoQuesID,fldQuesTitle,fldBody
> FROM tblFAQ_Question
> WHERE LCase(fldBody) LIKE '%airline%';
>
> This won't return any rows, even if there is a match in the field.
> Can anyone help me out?
>

I question whether there really is a match. Could you show us the row of
data that you feel should be returned by this statement?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[GH]

Bob Barrows [MVP] wrote:

> GH wrote:
>

>>I was trying to write an SQL statment that will do a string search in
>>an Access 2000 database. But I can't seem to figure out how to do it
>>so that the search is case-INsensitive. Here's the SQL I have so far:
>>
>>SELECT autoQuesID,fldQuesTitle,fldBody
>>FROM tblFAQ_Question
>>WHERE fldBody LIKE '%airline%';
>>
>>All this will do is search for "airline" in lower case. So if the
>>word "Airline" is in the field I'm searching in...it won't find it.

>
>
> Not according to the documentation, or my tests.
>
> This code:
> <%
> dim cn, rs, sSQL
> set cn=createobject("adodb.connection")
> sSQL="Select * From Table1 WHERE [Name] Like '%home%'"
> cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("db7.mdb")
> Set rs=cn.Execute (sSQL,,adCmdText)
> Response.Write rs.GetString(adClipString,," | ","<BR>")
> rs.Close:set rs=nothing
> cn.Close:set cn=nothing
> %>
>
> produced this result:
> 9 | Anti-Homeopathy Illuminati | 6
>
>
> According to the documentation, all searches are case-insensitive. This
> statement:
> Select * From Table1 WHERE [Name] = 'anti-homeopathy Illuminati'
>
> produced the same result. It's case-sensitive searches that are hard to do.
>
>

>>I've also tried this SQL statment:
>>
>>SELECT autoQuesID,fldQuesTitle,fldBody
>>FROM tblFAQ_Question
>>WHERE LCase(fldBody) LIKE '%airline%';
>>
>>This won't return any rows, even if there is a match in the field.
>>Can anyone help me out?
>>

>
>
> I question whether there really is a match. Could you show us the row of
> data that you feel should be returned by this statement?
>
> Bob Barrows

Ok, here's some data:

(tblFAQ_Question.fldQuesTitle)
Row 1: Airline Phone Numbers
Row 2: How do I make changes to my airline reservations?

If I type this SQL directly into an access query, I get no results:

SELECT tblFAQ_Question.fldQuesTitle
FROM tblFAQ_Question
WHERE (((tblFAQ_Question.fldQuesTitle) Like '%airline%'));

This makes even less sense, because now it isn't even finding the row
that matches it exactly ( row 2).

[Bob Barrows [MVP]]

GH wrote:

>
> If I type this SQL directly into an access query, I get no results:
>
> SELECT tblFAQ_Question.fldQuesTitle
> FROM tblFAQ_Question
> WHERE (((tblFAQ_Question.fldQuesTitle) Like '%airline%'));
>
> This makes even less sense, because now it isn't even finding the row
> that matches it exactly ( row 2).

Using the Access Query Builder, you have to use the Jet wildcards (*, ?)
instead of the odbc wildcards. Your query as typed into the Query Builder
should look like this (after removing all the unnecessary parentheses and
aliases):

SELECT fldQuesTitle FROM tblFAQ_Question
WHERE fldQuesTitle Like '*airline*'


It's only when constructing the sql statement using ADO (for example from an
ASP page) that you need to use the odbc wildcards.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[GH]

Bob Barrows [MVP] wrote:

> GH wrote:
>

>>If I type this SQL directly into an access query, I get no results:
>>
>>SELECT tblFAQ_Question.fldQuesTitle
>>FROM tblFAQ_Question
>>WHERE (((tblFAQ_Question.fldQuesTitle) Like '%airline%'));
>>
>>This makes even less sense, because now it isn't even finding the row
>>that matches it exactly ( row 2).

>
>
> Using the Access Query Builder, you have to use the Jet wildcards (*, ?)
> instead of the odbc wildcards. Your query as typed into the Query Builder
> should look like this (after removing all the unnecessary parentheses and
> aliases):
>
> SELECT fldQuesTitle FROM tblFAQ_Question
> WHERE fldQuesTitle Like '*airline*'
>
>
> It's only when constructing the sql statement using ADO (for example from an
> ASP page) that you need to use the odbc wildcards.
>
> Bob Barrows
>

You're right, when I use the * is does work in the query in access. So
if I was using PHP, then I would need to use the % instead of the *?

Thanks again for your help

[Bob Barrows [MVP]]

GH wrote:

>
> You're right, when I use the * is does work in the query in access.
> So
> if I was using PHP, then I would need to use the % instead of the *?
>
> Thanks again for your help

I don't know. I've never used PHP. If you use ODBC or OLEDB from PHP, then I
strongly suspect that you would need to use the odbc wildcards. Can anyone
confirm this?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[GH]

Bob Barrows [MVP] wrote:

> GH wrote:
>
>

>>You're right, when I use the * is does work in the query in access.
>>So
>>if I was using PHP, then I would need to use the % instead of the *?
>>
>>Thanks again for your help

>
>
> I don't know. I've never used PHP. If you use ODBC or OLEDB from PHP, then I
> strongly suspect that you would need to use the odbc wildcards. Can anyone
> confirm this?
>
> Bob Barrows
>

Yup, you're right. I just tried it in PHP and it works with the % and
not with the *. Thanks!