Kevin Miller

Trailing wildcards fail to %MATCH% text At the end of the field on Oracle9i

I have some software that generates SQL we support multiple versions of Microsoft SQL Server and Oracle. I ran into a failing test on Oracle today that was totally unexpected.

[Test]
public void Case_History_containing_a_queue_tag_should_be_destined_for_that_queue()
{
    string queueName = "SUPPORT";
    
    string caseHistory = String.Format("case is in queue {0}", queueName);

    string caseIDNumber = "275";
    
    SetCaseHistoryForCase(caseIDNumber, caseHistory);

    string ruleText = String.Format(@"(case_history contains '{0}') -> ""{0}""", queueName);

    string[] queues = AutoDestRule.RunRule("case", ruleText, caseIDNumber);

    Assert.AreEqual(1, queues.Length);
    Assert.AreEqual(queueName, queues[0]);
}

The Gist

There is a bit of noise in the test but the gist is that RunRule() generates and executes the following SQL:

SELECT count(*) FROM table_case WHERE id_number = '275' AND (case_history LIKE '%SUPPORT%')

Here is what the database looks like for table_case:

The test passes in Microsoft SQL Server but fails against Oracle. There is no difference in the SQL being generated between databases. I fired up Query Express to see what Oracle's problem is. Sure enough the query was returning a count of 0 on Oracle. The weird thing was if I modified the query ever so slightly it worked.

-- The missing T in SUPPORT returns a count of 1
SELECT count(*) FROM table_case WHERE id_number = '275' AND (case_history LIKE '%SUPPOR%')  

What in tar-nation is going on here?!

Trailing Wildcard Is To Blame

It turns out that because the value in the case_history field ends with the term being searched for that, for some reason in Oracle, the trailing % wildcard causes the constraint not to match. If I add anything to the end of of the field in the database the query matches. Which is why above when I only removed the last character from the LIKE clause it worked. For example if I change the case history to be this line.

string caseHistory = String.Format("case is in queue {0}. The case history no longer ends with the search term", queueName);

The test now passes. I don't know why Oracle 9i behaves like this. But it looks like they fixed it with Oracle 10g because I could not duplicate this problem there. Anyone know a good way to work around this problem other than upgrade to 10g?