Page 1 of 1

Remove alpha characters from an alpha-numeric string

Posted: Fri Aug 08, 2003 2:26 pm
by Crashin
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:

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.";
}
?>
Where regNumber is the field in question.

Thanks in advace for the advice!

Posted: Sat Aug 09, 2003 12:35 am
by Crashin
Okay, I'm getting closer. Now, I've got:

Code: Select all

<?php
if (isset($_POST['x']) && $_POST['x'] == 3) {
	$query = "INSERT INTO products 
		SELECT 
			productID, 
			REPLACE(regNumber, regNumber REGEXP '[^0-9]', ''), 
			customerID, 
			productName, 
			(CONCAT(SUBSTRING_INDEX(purchDate, '/', -1), '/', SUBSTRING_INDEX(purchDate, '/', 2))), 
			currentVersion, 
			(CONCAT(SUBSTRING_INDEX(versionDate, '/', -1), '/', SUBSTRING_INDEX(versionDate, '/', 2))), 
			dateModified 
		FROM 
			productsimport 
		WHERE 
			TRIM(customerID) != ''
	";
	$result = mysql_query($query) or die ('Sorry, Charlie!' . mysql_error());
	echo "Congratulations! There were " . mysql_affected_rows() . " rows entered into the products table.";
}
?>
The problem is it's stripping-out the 0's (zeros) from the values in addition to any other non-numeric characters. I've read through site after site about regular expressions but can't seem to find anything about this.