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 [link]

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.

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

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:
<code>
$sth=$dbh->prepare(qq{select substr(genre, 1, 1),genre FROM cds group by genre order by genre});
</code>
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]

SOLVED. I did a query in Perl:
<code>
$sth=$dbh->prepare(qq{select distinct(genre),id from cds group by genre order by genre});
print qq{[$id]\t-->$genre<--\n>};
</code>
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]

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]

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

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