MYSQL Replace() Function

MYSQL Replace() Function

The mySQL replace() function is a fantastic function for which you can select and change data from a database table. For example, let's assume one column in a table has lbs or cm next to it; but you want to make mathematical comparisons without the letters. Well, replace() allows you to remove the lbs or cm from the values. The replace() function can be used to replace all sorts of data from a string; such as whitespace, numbers and letters.

The example below shows how the cm and lbs is stripped from selected values from a table.

include('connect.inc');
$db = public_db_connect();

$command = "SELECT DISTINCT LOWER(firstname) as first, LOWER(lastname) as last, LOWER(Email) as email,  replace(replace(height,' ',''),'cm','') as height, replace(replace(weight,' ',''),'lbs','') as weight, date FROM table_sort WHERE id >0 ORDER BY last ASC" ;
$result = mysqli_query($db, $command);
while($row = mysqli_fetch_assoc($result)){
$firstname = $row['first'];
$lastname = $row['last'];
$email = $row['email'];
$height = $row['height'];
$weight = $row['weight'];
echo $firstname."-".$lastname."-".$email."-".$height."-".$weight."<br/>";
}