CODEWITHSUNDEEP
mysql
arok
arok

Reputation: 1845

My SQL query not retrieving all the data from the database

I have a table in my database with the name contact. It has the following columns: name, mobile and twon. The problem is that I'm trying to get all the twon='Dubai', but when I execute my query it only retrieves 81000 rows but the total was 130000. The other remaining rows are not appearing in my query.

My Query:

SELECT * FROM `contact` WHERE `twon` = 'Dubai'

Can anyone tell me where I am going wrong or help me to access all the data from table?

Upvotes: 0

Views: 559

Answers (2)

Raging Bull
Raging Bull

Reputation: 18767

= will fetch the record only if the column value is exactly 'dubai'.

Try with LIKE,TRIM and LOWER:

SELECT * FROM `contact` WHERE LOWER(TRIM(`twon`)) LIKE '%dubai%'

This query will fetch the records if twon column contains the word 'dubai'.

Upvotes: 2

birdypme
birdypme

Reputation: 194

Different things could be wrong.
If you post examples of rows that should be returned, it would help us.

  • Case Sensitivity

If case sensitivity is a problem (e.g. 'dubai' or 'DUBAI' are not returned), you can use the LOWER function

SELECT * FROM `contact` WHERE LOWER(`twon`) = 'dubai'
  • Extra blanks

In some cases, extra blanks in the column content would fail, for instance ' Dubai' and ' Dubai '. You can use the TRIM function to get rid of trailing and leading blanks.

SELECT * FROM `contact` WHERE TRIM(`twon`) = 'Dubai'
  • Combination

Combining the two will work, too.

SELECT * FROM `contact` WHERE LOWER(TRIM(`twon`)) = 'dubai'

Upvotes: 1

Related Questions