How to find special characters in SQL field?

Primary tabs

SQL, Caché

I like to use something like this:

select * from MyTable where MyFieldName like ‘%[^a-zA-Z0-9 !”%#$&”()*+,-./:;<=>?@]%’  

to find field that contains special characters that are only allowed.

  • 0
  • 0
  • 113
  • 4

Replies

Why is

select * from MyTable where MyFieldName like ‘%[^a-zA-Z0-9 !”%#$&”()*+,-./:;<=>?@]%’  

not a solution?

$TRANSLATE might be a possibility. It accepts a list of characters and replaces them either with other characters or just removes them. You could compare the length of the original column with the length of the column after using $TRANSLATE to remove illegal characters. For rows without illegal characters the length will match.


This would identify rows that have tilde (~), pipe (|), or backtick (`) in MyField:

SELECT * FROM MyTable WHERE CHAR_LENGTH($TRANSLATE(MyField,'~`|'))  <  CHAR_LENGTH(MyField)

It's worth noting that a statement like this can't make use of indices, so it will have to scan every row in the table.

Other than the obvious syntax error

like '%[^a-zA-Z0-9 !”%#$&”()*+,-./:\;<=>?@]%'

,the query fails to return anything at all (Special character or no special characters).