2

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?

James Z
  • 12,209
  • 10
  • 24
  • 44
Daniel Hudsky
  • 151
  • 1
  • 2
  • 7

3 Answers3

1

Try this code:

select Name from table where Name like '%[^0-9a-zA-Z ]%'
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0
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

Dale K
  • 25,246
  • 15
  • 42
  • 71
user2321864
  • 2,207
  • 5
  • 25
  • 35
0

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
Daniel Hudsky
  • 151
  • 1
  • 2
  • 7
  • http://stackoverflow.com/questions/928072/whats-the-regular-expression-that-matches-a-square-bracket escape them with \ or \\ `'%[^0-9a-zA-Z() -._/:=,\[\]]%'` – xQbert May 02 '17 at 17:55
  • 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. – Rory Apr 07 '22 at 09:36