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 !@#&()–[{}]:;',?/*
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'),
('()'),
('!@#&()–[{}]:;'',?/*'),
('`~$^+=<>“'),
('$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.