I was wondering if someone may be able to provide some insight into an Oracle issue a client of ours is having. I believe the issue may involve character sets, but I am not sure. We have narrowed the problem down to a field in the database called 'COMMENT' which is a VARCHAR2(4000). On my client site, when they do:
SELECT * FROM MYTABLE WHERE COMMENT LIKE '%1234%';
RESULT: 1 row selected
However, if they do:
SELECT * FROM MYTABLE WHERE CAST(COMMENT AS VARCHAR(4000)) LIKE '%1234%';
RESULT: 0 rows selected
If character sets have anything to do with the issue:
On the database server: NLS_NCHAR_CHARACTERSET=AL16UTF16
On the client: NLS_CHARACTERSET=WE8MSWIN1252
SELECT * FROM MYTABLE WHERE CAST(COMMENT AS VARCHAR2(4000)) LIKE '%1234%';