shrikant shrikant - 5 months ago 26
MySQL Question

Fetch all files from MySQL database which having special character but i don't want to use "or" condition for all special character in query

I have lot of files in Data base with some special character files name. So I want to fetch all files which is having special character.

I have written the blow query to fetch the files with special character.

For Example :

filename:

file!1.pdf
file$2.pdf
file!3.pdf
file*4.pdf
file&5.pdf
file$6.pdf
fileĀ£7.pdf
file8.pdf
file9.pdf
file10.pdf


The query is:

SELECT *
FROM file_managed
WHERE filename LIKE "%$%" OR filename LIKE "%!%" ;


or .........

It working fine but i have almost 25 special character to filter with. and i think this is not good query to achieve my requirement.

Can anyone help me to write perfect query , so that i no need to add "or" condition for all special character.
or pls suggest me.

Answer Source

Use regular expressions:

select fm.*
from file_managed fm
where filename regexp '[^a-zA-Z0-9._]'

This will look for alphanumerics, underscore, and period.

If you have a specific exclusion list, use the same idea:

select fm.*
from file_managed fm
where filename regexp '[!@#$%^&*()]'

Just add the list between the [ and ]. If those are invalid characters that you want to explicitly include, then use the regular expression escape character.