Azure Blog 5

What are 2 Ways to Return Rows That Contain Only Non-Alphanumeric Characters in MySQL

In this MySQL tutorial we have explain methods for 2 way returns rows that contain only Non-Alphanumeric Characters. You can easily master this by practicing a couple of times. See below how this tutorial goes by.

Characters such as [email protected]#&()–[{}]:;',?/* and then symbols like `~$^+=<>“, with the space or tab characters which are white space characters are called Non-Numeric or Alphanumeric characters. First we will use this query to build a sample table.

CREATE TABLE t1 (
    c1 varchar(255) NULL
    );

INSERT INTO t1 VALUES
    ('Music'),
    ('Live Music'),
    ('Café'),
    ('Café Del Mar'),
    ('100 Cafés'),
    ('[email protected]'),
    ('1 + 1'),
    ('()'),
    ('[email protected]#&()–[{}]:;'',?/*'),
    ('`~$^+=<>“'),
    ('$1.50'),
    ('Player 456'),
    ('007'),
    (null),
    (''),
    ('é'),
    ('É'),
    ('é 123'),
    ('ø'),
    ('ø 123');
SELECT c1 FROM t1;

Compare to [:alnum:]

First we will use the [:alnum:] POSIX character class and then we will use “REGEXP” along with NOT to negate.

SELECT c1 FROM t1 
WHERE c1 NOT REGEXP '[[:alnum:]]';

We can exclude a certain character as well using “NULLIF” method.

SELECT c1 FROM t1
WHERE NULLIF(c1, '') NOT REGEXP '[[:alnum:]]';

Specify a Range of Characters

We can specify the range of the character to filter non-numeric values.

SELECT c1 FROM t1
WHERE c1 NOT REGEXP '[A-Za-z0-9]';

And of course to remove empty string, we can do that.

SELECT c1 FROM t1
WHERE NULLIF(c1, '') NOT REGEXP '[A-Za-z0-9]';

We can also use “REGEXP_LIKE()” function.

SELECT c1 FROM t1 
WHERE NOT REGEXP_LIKE(c1, '[[:alnum:]]');

If you have any questions, please write in comments section.