Roland Bouman wrote:
> As for a solution to get it working, I can't think of anything better than to REPLACE each character a
> to z individually. Which seems rather clumsy, I know. I'm still thinking of a better way...I hope
> there is one...
## For academic purposes only (I know Roland, it's getting silly around here ;-)
USE test;
## The table
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c CHAR(20) NOT NULL
);
INSERT INTO foo (c) VALUES
('123ABC567'),
('XYX923'),
('121UVW');
## Create a temp table with integers up to the length of the phone column ...
DROP TABLE IF EXISTS t_ints;
CREATE TEMPORARY TABLE t_ints ( i INT NOT NULL);
INSERT INTO t_ints (i) VALUES
( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
## ... and create a temp table with the characters to be removed
DROP TABLE IF EXISTS t_chars;
CREATE TEMPORARY TABLE t_chars (c CHAR(1) NOT NULL);
INSERT INTO t_chars (c) VALUES
('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), ('i'),
('j'), ('k'), ('l'), ('m'), ('n'), ('o'), ('p'), ('q'), ('r'),
('s'), ('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z');
## Then make a decomposition of the allowed characters
DROP TABLE IF EXISTS tfoo;
CREATE TEMPORARY TABLE tfoo
SELECT
foo.c,
t_ints.i,
SUBSTRING(foo.c, t_ints.i, 1) AS x
FROM foo
JOIN t_ints ON t_ints.i <= LENGTH(foo.c)
WHERE
SUBSTRING(foo.c, t_ints.i, 1) NOT IN (SELECT c FROM t_chars)
ORDER BY foo.c, t_ints.i;
## And use this to update
UPDATE foo SET foo.c = (
SELECT GROUP_CONCAT(x ORDER BY i SEPARATOR '')
FROM tfoo
WHERE tfoo.c = foo.c
GROUP BY c
);
--
felix
Please use
BBCode to format your messages in this forum.