$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.
Why is
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:
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.
See also %MATCHES
Example:
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).