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

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?

sqlsql-serverregexdata-cleaning


Solution 1:[1]

Try this code:

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

Solution 2:[2]

Thank you for replying. I had tried your suggestions but I was still getting more results. However, it looks like you can get very specific with the exclusion. Eventually I ended up adding results from the data I got.

Like this: 

select Name from table where Name LIKE '%[^0-9a-zA-Z() -._/\:=,]%'

This finally gave me what I was looking for. Although new issue I have now is how to suppress the [] brackets which apparently also are found in the data:

  • ???? ?????
  • HP PCI 10/100Base-TX Core [100BASE-TX,FD,AUTO,TT=1500]
  • Fixed?????

Adding those brackets into the query breaks the array boundary: 

'%[^0-9a-zA-Z() -._/\:=,**[]**]%'

However, this is something I can handle. As long as I am not getting "all" the data.

Solution 3:[3]

LIKE '%[^0-9a-zA-Z]%'

numbers (0-9), lowercase alphas (a-z), uppercase alphas (A-Z). The "^" makes that a "NOT" one of these things

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Fahmi
Solution 2 James Z
Solution 3 Dale K

Related Questions