Icon View Incident Report

Serious Serious
Reported By: Larry Lustig
Reported On: 2/16/1999
For: Version 1.11 Build 1
# 306 SQL LIKE Operator Not Working Properly

SELECT with CONTAINS or LIKE "%Ab%" does not find records (works OK with LIKE "%A%").

This is tricky. Consider this SELECT statement:

SELECT t2.ClaimID, t2.FileID, t1.FirstName, t1.LastName, t1.SSN, t1.Phone, t2.WCBFileID, t2.InjuryDate, t2.EmployerOfficeID, t2.CarrierAgentID, t2.CarrierFileID
FROM people t1, claims t2 WHERE t1.PeopleID = t2.PeopleID
AND InjuryDesc LIKE "%As%" AND t2.CloseDate IS NULL

I have a table (attached) that has rows that contain these values in InjuryDesc (there are other rows as well):

Back,CTS,Asbestosis,Buttocks, Anxiety, Abdomen

The SELECT above returns the two rows correctly. However, change the LIKE clause to "%Ab%" and it does NOT return the single row that should be returned. Furthermore, change the clause to UPPER(InjuryDesc) LIKE "%AS%" or UPPER(InjuryDesc) LIKE "%AB%" and nothing is returned in either situation, even though 2 and 1 rows should be returned respectively.

Comments Comments
Problem was with the LIKE operator comparison matching logic.

Resolution Resolution
Fixed Problem on 2/17/1999 in version 1.12 build 1