Need a way of finding special characters in data using SQL query

Question

I am trying to find special characters in any of my fields that are not in the range of a-zA-Z0-9. However if I try this query:

select Name from table where Name like '%[?]%'

I get two records:

• ???? ?????
• Fixed?????

Which is what I want. However, since I don't know what the special chars will be I need to use an exclusion of data that has mixed characters:

select Name from table where Name NOT like '%[a-zA-Z0-9]%'

Since this excludes all records with a-zA-Z0-9 I only get:

• ???? ?????

But I also need to get the 'Fixed?????' result. I need to get the data that has the special character merged into it.

I am bit at a loss as how to do this. I've seen this done with shell scripts or 'vi' (LIST), but in SQL that's not so easy.

Has anyone out there solved this?

Solution

Try this code:

select Name from table where Name like '%[^0-9a-zA-Z ]%'

Comments

[Dan Bracuk]

The function you want is patindex.

[xQbert]

stackoverflow.com/questions/928072/… escape them with \ or \\ '%[^0-9a-zA-Z() -._/:=,\[\]]%'

[irag10]

in SQL you escape a LIKE clause like this ... WHERE x LIKE '%\[%' ESCAPE '\' Note you get to choose your escape character, so you can choose whichever you prefer.