Results 1 to 4 of 4

Thread: Replace Regexp

  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Replace Regexp

    Having this in a table "Titles":
    Code:
    Title
    -------
    The Labor
    Der Lancet
    La xxxx
    Sciences
    .
    .
    'd like to UPDATE Title by putting the start "The |Der |La " at the end of the string in one go, like "Labor (The) | Lancet (Der) | ...".

    Any way of using "REPLACE or TRIM" combined with "REGEXP" '^(The |DER |La )' ?

    something like this:
    Code:
    UPDATE Titles SET Title=REPLACE (Title, REGEXP......, '')

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by gtk

    Any way of using "REPLACE or TRIM" combined with "REGEXP" '^(The |DER |La )' ?
    Unfortunately, MySQL regular expression don't provide that kind of power at the moment.

    However, if you put your prefixes in a table:
    Code:
    CREATE TABLE prefixes (
    	prefix CHAR(10) NOT NULL PRIMARY KEY,
    	plength TINYINT NOT NULL DEFAULT 0,
    	KEY (plength)
    );
    
    INSERT INTO prefixes (prefix) VALUES
    ('De'), ('The'), ('Der'), ('La'), ('Le');
    
    UPDATE prefixes SET plength = LENGTH(prefix);
    you could use something like this:
    Code:
    UPDATE titles AS t
    SET t.title = CONCAT(
    	SUBSTRING(
    		t.title, 
    		2 + (
    				SELECT p.plength 
    				FROM prefixes AS p 
    				WHERE LOCATE(p.prefix, t.title) = 1 
    				ORDER BY p.plength DESC 
    				LIMIT 1
    			)
    	), 
    	' (',
    	(
    		SELECT p.prefix 
    		FROM prefixes AS p 
    		WHERE LOCATE(p.prefix, t.title) = 1 
    		ORDER BY p.plength DESC 
    		LIMIT 1
    	),
    	')'
    	)
    WHERE
    	EXISTS (
    		SELECT * 
    		FROM prefixes AS p 
    		WHERE LOCATE(CONCAT(p.prefix, ' '), t.title) = 1
    	);
    --
    felix

  3. #3
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by felixg
    you could use something like this:
    Code:
    Overly complex code snipped
    or the much simpler
    Code:
    UPDATE titles t, prefixes p
    SET t.title = CONCAT(SUBSTRING(t.title, p.plength+2), ' (', p.prefix, ')')
    WHERE
    	SUBSTRING(t.title, 1, p.plength+1) = CONCAT(p.prefix, ' ');

    --
    felix[/QUOTE]

  4. #4
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Thank you for your time,
    in fact it was just to know if "regexp replace" is possible.

Posting Permissions

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