Select Only Rows That Contain Only Alphanumeric Characters in MySQL

SELECT only rows that contain only alphanumeric characters in MySQL

Try this code:

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'

This makes sure that all characters match.

Regex that contain only alphanumeric and special characters in MySQL

The special character i want: - : ( ) & . , ? ! ' | < >

Note that when you add characters to a character class, you do not have to blindly escape all of them, and if you have to escape them, you must use a double \\ backslash in MySQL regex. However, it is not necessary. When you place the - hyphen at the start or end of the character class, or right after a POSIX character class, it is treated as a literal hyphen. Other "special" characters you need do not have to be escaped in the character class.

Also, ' should be doubled in a single quoted string literal.

So, use

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9 :()&.,?!''|<>-]+$'

Properly select actual non-alphanumeric characters in MySQL column

Have you tried:

SELECT * FROM table WHERE name REGEXP "[^a-z0-9]+" LIMIT 0,10;

MySQL query - retrieve rows that only contain letters and spaces

You can use regular expression for this, But this will be little slower.

WHERE `geocache_place` RLIKE '^[[:alnum:]]+$';

Top location can be found by adding a ORDER BY clause on the expression how you want to determine the top.

How to get all rows that contain characters others than [a-zA-Z] in MySQL

Match the whole text

SELECT * 
FROM contact_info 
WHERE address NOT REGEXP '^[a-zA-Z0-9]*$';

How to extract only String part from a column containing alphanumeric String in mysql

MySQL does have any native regex replacement support, at least not of the time this answer was written. However, if you just want the letter components, you may try just removing all digits:

SELECT
    col,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(col, '9', ''), '8', ''), '7', ''), '6', ''), '5', ''), '4', ''),
         '3', ''), '2', ''), '1', ''), '0', '') AS alpha_only
FROM yourTable;

Demo

Return rows where field contains non alpha-numeric characters

The question you are referring to is for SQL Server. To do what you want in SQL Server, you would do:

where col like '%[^a-zA-Z0-9]%'

The ^ in the like pattern matches any characters not in the list.

---

Related Topics