SQLite Forum

Disparity in results using like and '='
Login

Disparity in results using like and '='

(1) By Trudge on 2020-11-03 22:21:34 [source]

I get different results from a query depending on whether I use 'like' or '=', and now need to find out which records are different.

The column I'm accessing is 'text' and contains short strings. I am looking for "Jazz - Fusion". That is the string "Jazz" + the string " - " (space hyphen space) + the string "Fusion".

When I use a 'like' I get 1100 results, but when I use '=' I get 1098 results.

sqlite> select count() from cds where genre like "Jazz%"; 1100 sqlite> select count() from cds where genre = "Jazz - Fusion"; 1098

How can I find out which records have some odd character/s? I'm using Perl on a Mac to create a web page, with this command to pull out the first letter of each genre: $sth=$dbh->prepare(qq{select substr(genre, 1, 1),genre FROM cds group by genre order by genre}); but what I get is: A B C E F J J R W.

So it seems there are 2 'Jazz - Fusion' entries, and I can't determine where the difference is. I'm thinking one of the hyphens is a 'figure dash' or some such. Anyone have an idea how I can find the culprit?

(2) By Trudge on 2020-11-03 23:44:57 in reply to 1 [link] [source]

SOLVED. I did a query in Perl: $sth=$dbh->prepare(qq{select distinct(genre),id from cds group by genre order by genre}); print qq{[$id]t-->$genre<--n>}; and discovered 1 entry had several trailing spaces. Once they were deleted, all was good.

(3) By Gunter Hick (gunter_hick) on 2020-11-04 07:09:12 in reply to 1 [link] [source]

You could also have used the compound operator EXCEPT to isolate the difference set.

(4) By Trudge on 2020-11-04 12:28:44 in reply to 3 [link] [source]

I have much to learn about sqlite - Thank you. Coming from MySQL.

(5) By Wout Mertens (wmertens) on 2020-11-04 15:31:14 in reply to 1 [link] [source]

I know you found it but next time you can do "genre like 'Jazz%' and genre <> 'Jazz - Fusion'"