🚀 go-pugleaf

On Thu, 21 Jan 1999 00:24:14 +0100, "Jens Korth" <XJens.KorthX@wanadoo.fr>
wrote:

>thanks for your explanation and the details which functions do not work with
>memofields!
>
>But how do people work with SQL if it does not support these functions?
>I am sure I am not the only one with memofields?!
>
>Or do other SQL implementations support these things?

Different database systems provide varying levels of support for BLOB text
(aka memo) columns. Some provide more, some less. From what I have seen
(mind you, I am not an expert in this area), the SQL-92 specification does
not dictate the compliancy requirements well enough or at all. Thus, the
degree of variance in supporting memo columns across difference database
systems. Personally, I feel this should be supported in local SQL and will
be reporting this in our development bug-reporting system. It may not be a
bug per se, but it certainly qualifies as an improvement suggestion.

In the meantime, there are a few things you can do. One is to use QBE. The
Database Desktop utility (DBD) provides a graphical facility for QBE
querying. Such queries can be saved to file and used in a local SQL
statement in the place of a table. Simply refer to the .QBE file in the
FROM clause where you would otherwise have referred to a table.

In the DBD's QBE interface, filtering based on a column containing a
substring somewhere within the column is done by using the expression
below. The filtering should be, by default, case-insensitive.

  ..some text..

Saved to a file, this QBE query could subsequently be used in a local SQL
statement like that below.

  SELECT *
  FROM "YourQuery.qbe"

By doing this, you can take advantage of the different querying
capabilities provided by QBE while using SQL.

Saved QBE queries are simply text files using a very specific format.
Because these are text files, you could write your own QBE queries without
needing to use the DBD's graphical, design-time interface. Save a QBE query
to a file and look at its format. Visual dBASE provides the ability to
access and build text files, so once you know the structure of a valid QBE
file you will be able to build them from dBASE.

If you do not wish to use or cannot use QBE, you can still perform
case-insensitive filtering against a memo column using just SQL. To do
this, you would need to provide conditions in the WHERE clause appropriate
for any combination of capitalization the text might be in. For example, in
English any ways, a word or phrase would typically be entered in one of
three variations of capitalization:

1. All upper case ("quick brown fox").
2. All lower case ("QUICK BROWN FOX").
3. First letter of each word capitalized ("Quick Brown Fox").

So most queries would really only require these three conditions for a
single case-insensitive filtering against a memo column.

  SELECT *
  FROM "YourTable.dbf" Y
  WHERE ((Y.MemoCol LIKE "%quick brown fox%") OR
    (Y.MemoCol LIKE "%QUICK BROWN FOX%") OR
    (Y.MemoCol LIKE "%Quick Brown Fox%"))
    /* followed by any other filtering conditions */

//////////////////////////////////////////////////////////////////////////
Steve Koterski                       "For people who like peace and quiet:
Technical Publications               a phoneless cord."
INPRISE Corporation                                             -- Unknown
http://www.inprise.com/delphi