MySQL Forums :: Newbie :: Regexp, replace ??? what do I need to use

Sandra Rascheli Wrote:

> Hello everybody, I am new to regular expressions
> and I need to know if they can help in solving
> this problem, I have a field in a table called
> "identification" which is a text field that
> includes dots (.), commas (,), hyphens (-) and
> letters, plus numeric characters. What I want to
> do is to extract ONLY the numeric characters from
> the field.

Unfortunately, they cannot.
If you are using MySQL >= 5, your best bet is writing a stored procedure.

Alternatively, you can use something silly like this:

USE test;

-- Helper table
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY);

-- make sure you have enough of them
INSERT INTO ints (i) VALUES
( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

DROP TABLE foo;
CREATE TABLE foo (identification CHAR(20) NOT NULL);
INSERT INTO foo (identification) VALUES
('V-1.234.567'),
('X-ABC-123.9');

SELECT
    identification, 
    GROUP_CONCAT(SUBSTRING(identification, i, 1) ORDER BY i SEPARATOR '')
FROM foo
JOIN ints ON i BETWEEN 1 AND LENGTH(identification)
WHERE
    SUBSTRING(identification, i, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY identification;

In my opinion, MySQL urgently needs some powerful builtin search-and-replace string functions, preferably based on a regex engine.

--
felix
Please use BBCode to format your messages in this forum.