mysql has not support regexp_replace function

These days I want to do some car owner database clearance.
I want to replace the car brand only use Chinese name like “福特福克斯” not “福特福克斯2.0″；
So I write a SQL statement and only delete 0 or 1 :

update namecard set brand = replace(brand,"[^\u4e00-\u9fa5]","");
SELECT brand , replace(brand,brand REGEXP"[\u4e00-\u9fa5]","-") from namecard limit 0,100;

I search some times at Google and Baidu to find the how to resolve the problem,But it’s a bad news MYSQL can not support regexp_replace function like ORACLE.
REGEXP only return 0,1 means true or false.

The last method maybe use PHP.

define("DBSERVER", "localhost");

define("DBNAME", "carowner");

define("DBUSER", "root");

define("DBPASSWORD", "tina0913");

define("PERPAGE", "1000");

$arrCarOwner = listRegexp();

if (count($arrCarOwner) > 0) {

	updateDatabase($arrCarOwner);

	$int = $_REQUEST['PB_page'] + 1;

	echo '





' . $int . '



';

} else {

	echo "Finished";

}
function updateDatabase ($arrCarOwner)

{

	foreach ($arrCarOwner as $arrCarOwner) {

		$dbh = & db_conn();

		$brand = regexp_replace($arrCarOwner['brand']);

		$strQuery = "UPDATE `namecard` SET

		 `brand` = '" . $brand . "'

		 WHERE (`id` = '" . ($arrCarOwner['id']) . "')";

		$result = $dbh->query($strQuery);

	}

	return TRUE;

}
function regexp_replace ($str)

{

	$pattern = '/[u4e00-u9fa5]/';

	$replace = "";

	$str = preg_replace($pattern, $replace, $str);

	return $str;

}
function listRegexp ()

{

	if (isset($_REQUEST['PB_page'])) {

		$intOffset = ($_REQUEST['PB_page'] - 1) * PERPAGE;

	} else {

		$intOffset = 0;

	}
	$dbh = & db_conn();

//	$strQuery = 'SELECT namecard.id,namecard.brand FROM namecard';

	$strQuery = 'SELECT namecard.id,namecard.brand FROM namecard WHERE brand like ';

	$strQuery .= " LIMIT " . $intOffset . "," . PERPAGE;

	$sth = $dbh->prepare($strQuery);

	$sth->execute();

	$result = $sth->fetchAll(PDO::FETCH_ASSOC);

	return $result;

}
function &db_conn ($dbuser = null, $dbname = null, $pass = null, $server = null)

{

	if (!$dbuser) {

		$dbuser = DBUSER;

	}

	if (!$dbname) {

		$dbname = DBNAME;

	}

	if (!$pass) {

		$pass = DBPASSWORD;

	}

	if (!$server) {

		$server = DBSERVER;

	}
	$option = array (PDO::ATTR_PERSISTENT => TRUE);

	try {

		$db = new PDO('mysql:host=' . $server . ';dbname=' . $dbname, $dbuser, $pass, $option);

		$db->exec('SET NAMES UTF8');

	} catch (PDOException $e) {

		echo $e->getMessage();

		die();

	}

	return $db;

}
?>

						
			

			  
				April 29th, 2009 in
				php_mysql_js_css												| 884 views