Searching for a specific text value in a column in SQLite3

Suppose I have a table named ‘Customer’ with many columns and I want to display all customers who’s name ends with ‘Thomas'(Lastname = ‘Thomas’). The following query shows an empty result(no rows). Also it didn’t show any error.

SELECT * FROM Customer WHERE Lastname = 'Thomas';

While executing the following query give me correct result.

SELECT * FROM Customer WHERE Lastname LIKE '%Thomas%';

I would like to know what is the problem with my first query. I am using sqlite3 with Npm. Below is the result of ‘.show’ command(Just in case of the problem is with config).

sqlite> .show
     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width: 

Answer

1. Use Like instead of =
2. Trim to ensure that there arent spaces messing around

so the query will be

SELECT * FROM Customer WHERE trim(Lastname) LIKE 'Thomas';

depending on your types, probably you dont need point 2, since as can be read in mysql manual

All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces

But the point 1 could be the solution. Actually if you want to avoid problems, you should compare strings with LIKE, instead of =.

If You still have problems, probably you will have to use collates.

 SELECT *
 FROM t1
 WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci; #using your real table collation

more information here But specifically with ‘Thomas’ you shouldn’t need it, since it hasn’t got any special characters.