How to find special characters in SQL field?
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.
Comments
$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).
See also %MATCHES
Example:
<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080"> from </FONT><FONT COLOR="#008000">MyTable </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">MyFieldName </FONT><FONT COLOR="#000000">%MATCHES </FONT><FONT COLOR="#008080">'[~|`]*'</FONT>
Why is
select * from MyTable where MyFieldName like ‘%[^a-zA-Z0-9 !”%#$&”()*+,-./:;<=>?@]%’
not a solution?