MYSQL REGEXP REPLACE - Proving very difficult...

avera · #1 (**permalink**) 04-13-09, 11:17

Hi Everyone,

So - I have the following problem - here's my query:

Code:

SELECT
	CONCAT('C-', `idcategories`, '-', LOWER(REPLACE(categoryname, categoryname REGEXP '[^a-zA-Z0-9]', '-')), '.html')
AS
	`link`
FROM
	categories

This partially works, I get the following results:

Code:

C-4-electronics.html
C-5-audio equipment.html
C-6-home entertainment.html
C-7-photography.html
C-8-portable audio.html
C-9-televisions.html
C-10-amplifiers & receivers.html

But there is a problem. My regexp does not seem to be working.

Could anyone please help?

Is there a reason this does not work, because it looks correct and kind of works!

P.S. I am trying to strip out all none-alpha-numeric chars and replace them with a hyphen "-".

avera · #2 (**permalink**) 04-13-09, 11:18

Just a heads up - I cannot reverse the query because there is no WHERE clause.

(Someone asked me that on anoither forum.)

r937 · #3 (**permalink**) 04-13-09, 12:04

REGEXP can only return "yes" or "no" whether it finds a match

you will have to replace the unwanted characters individually (nested REPLACE functions)