MYSQL search if a string contains special characters?

Question

I need to search table field contains special characters. I found a solution given here something like:

SELECT * 
FROM `tableName` 
WHERE `columnName` LIKE "%#%" 
OR `columnName` LIKE "%$%" 
OR (etc.)

But this solution is too broad. I need to mention all the special characters. But I want something which search something like:

SELECT *
FROM `tableName`
WHERE `columnName` LIKE '%[^a-zA-Z0-9]%'

That is search column which contains not only a-z, A-Z and 0-9 but some other characters also. Is it possible with MYSQL

Does anyone know the answer for the same question but in Snowflake instead of MySQL? I tried below mentioned solution but it did not work. — Rahul Ghadge, Oct 22 '20 at 20:21

score 37 · Accepted Answer · answered Nov 05 '15 at 07:02

37

Use regexp

SELECT *
FROM `tableName`
WHERE `columnName` REGEXP '[^a-zA-Z0-9]'

This would select all the rows where the particular column contain atleast one non-alphanumeric character.

or

REGEXP '[^[:alnum:]]'

answered Nov 05 '15 at 07:02

Avinash Raj

5

I was looking for any non alphanumeric also. But, I also wanted to allow hyphen and spaces. This the regex I used for that. REGEXP '[^a-zA-Z0-9[.hyphen-minus.][.space.]]' – panofish Feb 27 '17 at 18:50
`I was looking for any non alphanumeric also`, I think space, hyphen is also an non-alphanumeric number. – Avinash Raj Feb 27 '17 at 19:02
Working perfectly. Thanks. – Anurag Prashant Mar 29 '19 at 07:21

1 Answers1