Search code examples
mysqlsqldatabasesql-delete

Delete records using sql ,if it is greater than certain number with an unit attached to it (eg 3M or 3cm)

let a table be 'hotel' , with a column :

hotel_worth
15M
4M
8M
3M
1M
2M
11M

how do i delete the records in the table using sql ,where worth is greater than 3M.

Solution

  • In the WHERE clause of the DELETE statement you can convert the string to a numeric value by adding 0:

    DELETE FROM hotel
WHERE hotel_worth + 0 > 3

    If you want to delete only rows with the suffix M (if there are other suffixes also) add:

    AND RIGHT(hotel_worth, 1) = 'M'