Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: MYSQL REGEXP REPLACE - Proving very difficult...

    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 "-".
    Last edited by avera; 04-13-09 at 15:33.

  2. #2
    Join Date
    Apr 2009
    Posts
    3
    Just a heads up - I cannot reverse the query because there is no WHERE clause.

    (Someone asked me that on anoither forum.)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    REGEXP can only return "yes" or "no" whether it finds a match

    you will have to replace the unwanted characters individually (nested REPLACE functions)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2009
    Posts
    3
    Yeah - I noticed that it returns 1 or 0.

    But I would have imagined that would have given the REPLACE some indication of a match.

    Any other ideas?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by avera
    Any other ideas?
    yes -- use two columns instead of one, and use your application language (php?) to store the two different strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •