Board index » delphi » Searching a BLOB Memo Field Using TQuery.SQL

Searching a BLOB Memo Field Using TQuery.SQL

Using TQuery.SQL in Delphi 3, How do I perform a case-insensitive search
for a substring located anywhere in a BLOB Memo field?

William Vaughn
wvau...@tri-v.com

 

Re:Searching a BLOB Memo Field Using TQuery.SQL


Quote
>Using TQuery.SQL in Delphi 3, How do I perform a case-insensitive search
>for a substring located anywhere in a BLOB Memo field?

Unfortunatly that is not supported in Local SQL

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Searching a BLOB Memo Field Using TQuery.SQL


On Sat, 22 Jan 2000 19:10:43 -0600, "William Vaughn" <wvau...@tri-v.com>
wrote:

Quote
>Using TQuery.SQL in Delphi 3, How do I perform a case-insensitive search
>for a substring located anywhere in a BLOB Memo field?

You can use the LIKE predicate to compose a filter based on a string
appearing anywhere within a MEMO column's contents.

  SELECT *
  FROM AnyTable
  WHERE (MemoCol LIKE "%abc%")

However, this comparison is case-sensitive and local SQL does not allow you
to use the UPPER and LOWER functions on MEMO columns to negate that
case-sensitivity. The best you can do in SQL is to provide multiple
conditions, each using the one of the most likely casing schemes the text
will appear in: all lower case, all upper case, or first letter upper case
and the rest lower case.

  SELECT *
  FROM AnyTable
  WHERE ((MemoCol LIKE "%abc%") OR
    (MemoCol LIKE "%ABC%") OR
    (MemoCol LIKE "%Abc%"))

If the source table isa local type (Paradox, dBASE, or FoxPro) an
alternative would be to filter using a QBE file. Create a QBE query using
the Database Desktop utility (or Paradox or a text editor) and save it to a
file. String comparisons like this are case-insensitive, so this filtering
fits your needs. To use that .QBE file in Delphi, compose an SQL statement
in a TQuery that uses the .QBE file like you would ordinarily use a base
table file.

  SELECT *
  FROM "Test.qbe"

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Other Threads