Remove alpha characters from an alpha-numeric string
Posted: Fri Aug 08, 2003 2:26 pm
Hi all. I've got some data in a field that generally contains numeric information, but a few entries have an alpha character and a few are empty. Selecting all but the empty records is easy enough, but I do need to grab the entries with the alpha chars. but only the numeric part of them.
For example, a record in the table might have a "reg. number" like 12345m. I only want to grab the 12345 part of it. I've looked in the MySQL manual and didn't find any functions to strip non-numeric characters.
Ultimately, what I'm doing is pulling all of the data out of one very relaxed table and putting it into another with more restrictions. My code looks like:
Where regNumber is the field in question.
Thanks in advace for the advice!
For example, a record in the table might have a "reg. number" like 12345m. I only want to grab the 12345 part of it. I've looked in the MySQL manual and didn't find any functions to strip non-numeric characters.
Ultimately, what I'm doing is pulling all of the data out of one very relaxed table and putting it into another with more restrictions. My code looks like:
Code: Select all
<?php
if (isset($_POST['x']) && $_POST['x'] == 3) {
$query = "INSERT INTO products
SELECT
productID,
regNumber,
customerID,
productName,
(CONCAT(SUBSTRING_INDEX(purchDate, '/', -1), '/', SUBSTRING_INDEX(purchDate, '/', 2))),
currentVersion,
(CONCAT(SUBSTRING_INDEX(versionDate, '/', -1), '/', SUBSTRING_INDEX(versionDate, '/', 2))),
dateModified
FROM
productsimport
";
$result = mysql_query($query) or die ('Sorry, Charlie!' . mysql_error());
echo "Congratulations! There were " . mysql_affected_rows() . " rows entered into the products table.";
}
?>Thanks in advace for the advice!