mysql query not giving correct results

Question

I have a table called records which has several columns, one of which is fromphone which represents a phone number. I can run this query to see the different phone numbers:

SELECT DISTINCT fromphone
FROM records

and it shows the different phone numbers.

However, if I run this query:

SELECT *
FROM records
WHERE fromphone = '123-456-7890'

where fromphone is a phone number in the table, no results get returned.

Answer 1

Are you sure there is a record with phone number as 123-456-7890 in the database table? There maybe some leading or trailing space in it.

Try to use LIKE and %% operator, do it like this:

SELECT *
FROM records
WHERE fromphone LIKE '%123-456-7890%'

Answer 2

The most likely scenario is that the exact string, '123-456-7890' is not in that column. Try using LIKE instead of =, in case there's some extra spaces or something thats causing the equal not to match the phone number.

Answer 3

If you have leading/trailing spaces, you can just use TRIM()

SELECT *
FROM records
WHERE TRIM(fromphone) = '123-456-7890'

Using LIKE with the leading and trailing wildcard can get very slow if you have a lot of rows in the records table because MySQL has to do a full table scan when you do that.