I have a field that's VARCHAR(30). It's a price field, where the user can enter whatever they want. So in the database, I might have anything like (but not limited to) the following:

NULL (no price entered)
$3,000
300
300.00
$300.00
$300-$400.00
$300 obo
Best offer
$300 each, $500 for both
$300 or trade
Trade for guns or $300
I don't know, around 300 or so (I'm not joking, I have something like this)
three hundred dollars

I just print the field to the screen so it's worked just fine for about 7 years, but now I'm wishing that I'd limited it to numbers. Because I would like to offer a search option letting the user search for items between $_GET['low'] and $_GET['high'].

A normal query would look like:

sprintf(<<<EOF
SELECT * FROM table WHERE 
 price >= %s AND
 price <= %2
EOF,
 mysqli_real_escape_string($_GET['low']),
 mysqli_real_escape_string($_GET['high']));

But how do I make a logical modification to this query to make it only recognize the numbers in price?

I guess, realistically, I would need it to remove commas (eg, 3,000), then ignore any non-numeric character before the first number, then ignore everything after the second non-numeric character?

I don't know how to do this in MySQL at all, so right now I'm thinking about doinging the query without the price query, then in PHP loop through and convert each price to a number and compare it.

Something like:

while($row = mysqli_fetch_row($sth)) {
 if ($_GET['low'] || $_GET['high']) {
  list($id, $username, $price) = $row;

  $price = str_replace(',', '', $price);
  $price = preg_replace('#^[^0-9]+#', '', $price);
  $price = preg_replace('#([0-9]+).*#', '$1', $price);

  if ($price >= $_GET['low'] && $price <= $_GET['high'])
   array_push($arr, $row);
 }

 else array_push($arr, $row);
}

Thoughts?