Trailing wildcards fail to %MATCH% text at the end of the field on Oracle9i
Kevin ran into a weird Oracle issue this week in one of his tests.
Setup: I have case id 275 with a case_history of “case is in queue SUPPORT”
select count(*) from table_case where id_number = ‘275’ and case_history like %SUPPOR%’
This query returns 1 row, as expected.
select count(*) from table_case where id_number = ‘275’ and case_history like %SUPPORT%’
This query returns 0 rows. I was expecting it to return 1 row.
I don’t understand why the 2nd query doesn’t return the expected row.
I would have expected that the percent character would have matched to zero or more characters.
The problem happens on Oracle 9. Everything works as expected in Oracle 10.
Can anyone explain this behavior?