We help IT Professionals succeed at work.
Get Started
Select from table in Oracle returning wrong results when using CAST
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=AL1
On the client: NLS_CHARACTERSET=WE8MSWIN1
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=AL1
On the client: NLS_CHARACTERSET=WE8MSWIN1
Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE