adding a concatenated field in an SQL query breaks LIKE

[Peter Haworth]

SQLite is case sensitive so "bank" is not the same as "Bank". You can fix
that either by defining the column containing "bank" to be COLLATE NOCASE
or use the UPPER function around the last concatenation, then LIKE '%BANK%'.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Sat, Jul 12, 2014 at 4:46 PM, Dr. Hawkins <dochawk at gmail.com> wrote:

> I have the query
> SELECT uniqDna,  cname, cadr , reason, asset FROM vader_darth______001_dna
> WHERE (cname || cadr NOT NULL) AND (chrType <> 'K') AND ((cname || cadr ||
> reason || asset)  LIKE '%bank%') ORDER BY cname ASC, cadr ASC ;
>
> As written, it incorrectly only returns the first result with "Bank" in it.
>
> If I remove the fourth concatenation, ||asset, it correctly finds all
> instances with "Bank" in any field (5 instances).
>
> If I reduce my search from "Bank" to "B", it finds the other instances with
> B (or at least man)--but leaves out the other 4 with "Bank" !!
>
> This is for in-memory SQLite, on 5.5.4
>
>
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>