avi avi - 27 days ago 9
SQL Question

MySQL remove all alphanumeric chars from string except two chars

I have this function to remove all alphanumeric chars:

DROP FUNCTION IF EXISTS alphanum;
DELIMITER |
CREATE FUNCTION alphanum( str CHAR(32) ) RETURNS CHAR(16)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP '[[:alnum:]]' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;


How do i modify it to allow also these two chars:
[
,
]
?

for example:

alphanum('hell;o [world] number8$')


will give:
hello[world]number8

Answer

Change:

 IF c REGEXP '[[:alnum:]]' THEN 

To:

 IF c REGEXP '[[:alnum:]\[\]]' THEN 

EDIT:

I guess I never noticed this in the MySQL documentation:

To include a literal ] character, it must immediately follow the opening bracket [.

 IF c REGEXP '[\]\[[:alnum:]]' THEN 

I'm not sure if the backslash is necessary, so this might work:

 IF c REGEXP '[]\[[:alnum:]]' THEN 

Or:

 IF c REGEXP '[[.left-square-bracket.][.right-square-bracket.][:alnum:]]' THEN 
Comments