CODEWITHSUNDEEP
mysql
Sailendra Varma
Sailendra Varma

Reputation: 29

Delete records From table based on certain criteria

I have a table with three columns:

NAME, MOBILE, CITY

It does not have any unique column.

While dumping data from excel the fields haven't been be mapped correctly, as a result mobile numbers have been dumped into CITY column. Now i have to clean those, there are some 10 million records.

The CITY column consists of both city data and mobile data.

Any Idea on how do i clean up?

Upvotes: 0

Views: 41

Answers (1)

Glorfindel
Glorfindel

Reputation: 22651

Let's assume all cities start with a letter, and everything that doesn't is a telephone number. Then you could do the following

DELETE FROM table
  WHERE LEFT(city, 1) NOT BETWEEN 'a' AND 'z'
    AND LEFT(city, 1) NOT BETWEEN 'A' AND 'Z'

Upvotes: 1

Related Questions