adding a concatenated field in an SQL query breaks LIKE

[Peter Haworth]

Correction to my earlier post.  SQLite is case sensitive with simple
comparisons like "=" but the LIKE operator is not case sensitive so there
is something else going on.

It's hard to discern the issue from your post because every SELECT
statement in it is exactly the same except one has "alder" and the rest
have "alde" and none except the first match the results you included.

I've used LIKE statements on concatenated columns in the past and never had
a problem so I'll guess that there's some data problem with the column that
you add to the concatenation when it fails.  What happens if you use that
column instead one of the others, same result or different?

If all else fails, you could use:

(cname LIKE '%bank%' OR cadr LIKE '%bank' OR reason 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 7:20 PM, Dr. Hawkins <dochawk at gmail.com> wrote:

> On Sat, Jul 12, 2014 at 6:11 PM, Peter Haworth <pete at lcsql.com> wrote:
>
> > 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%'.
> >
>
> It doesn't seem to be once LiveCode is done with it :)
>
> Having "alder" in my entry field  gives the query
>
> SELECT uniqDna, cname, cadr, reason, asset FROM vader_darth______001_dna
> WHERE ((cadr NOT NULL) AND (chrType<>'K') AND ((cname ||cadr  || reason)
> LIKE '%alder%'  )) ORDER BY cname ASC, cadr ASC;
>
> Which yields
>
> 808    Alderan Nobility    Hidden quite well,
> thank you!    Guarantee of deposits at Bank of Aldaran
>
> (which is the correct result).
>
> "ban" produces query
>
> SELECT uniqDna, cname, cadr, reason, asset FROM vader_darth______001_dna
> WHERE ((cadr NOT NULL) AND (chrType<>'K') AND ((cname ||cadr  || reason)
> LIKE '%alde%'  )) ORDER BY cname ASC, cadr ASC;
>
> and result  (vtab replaced with double-cr)
>
> 808    Alderan Nobility    Hidden quite well,
> thank you!    Guarantee of deposits at Bank of Aldaran
>
> 592    Bank of America    4161 Piedmont Pkwy
> Greensboro, NC  27410-8110    through
> credit card
>
> 601    Bank of America    attn: Bankrutpcy
> NC4-105-0314
> Box 26012
> Greesnboro, NC  27420-6012    Suddenly, on a dark and stormy night, Audrey
> dreamed of Seymore    Forest Moon of Endor
>
> 596    Citi Cards    Centralized Bankruptcy
> Box 20483
> Kansas City, MO  6419    through
> credit card
>
> 595    Merrick Bank    Box 5000
> Draper, UT  8240    through
> credit card
>
> However, when I add one more concatenated field for the LIKE to search
> through, I get the query (which seems correct)
>
> SELECT uniqDna, cname, cadr, reason, asset FROM vader_darth______001_dna
> WHERE ((cadr NOT NULL) AND (chrType<>'K') AND ((cname ||cadr  || reason)
> LIKE '%alde%'  )) ORDER BY cname ASC, cadr ASC;
>
> But I get only
>
> 601    Bank of America    attn: Bankrutpcy
> NC4-105-0314
> Box 26012
> Greesnboro, NC  27420-6012    Suddenly, on a dark and stormy night, Audrey
> dreamed of Seymore    Forest Moon of Endor
>
> (the third result from the other Query).
>
> There are no indices created; the table in memory comes from
>
>     "CREATE TABLE " & dhtbl_dna  & \
>    " (uniqDna integer primary key , usr VARCHAR(20), tstmp TEXT, "  & \
>    the stCrDbStr of stack "dna" & " , ch BOOLEAN, scr TEXT );"  & cr
>
> (where the stCrDbStr is just a bunch of name/type pairs generated
> elsewhere)
>
> What I'm seeing is that by concatenating one more field (reason) to the
> prior concatenation of three fields, (correct) matches which previously
> occurred no longer match.  (reason & asset are VARCHAR 254 & 154, in case
> that matters)
>
> It comes down to A matching long string B as a substring, but not once B
> gets longer.
> --
> 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
>