I have the following SELECT statement, which works:
SELECT FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE ‘%SMITH%’ AND (GENDER=‘M’ OR GENDER IS NULL) ) AND ( DOB_SQL=‘1959-05-16’ AND FIRST_C=‘T’ )
However, when I modify it as follows:
SELECT FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE ‘%SMITH%’ AND (GENDER=‘M’ OR GENDER IS NULL) ) AND ( DOB_SQL=‘1959-05-16’ AND FIRST_C=‘T’ AND MIDDLE_C=‘A’)
it yields no results. I know there is a record that matches the criteria specified. What am I doing wrong?
If I list out the field values, I see the matching value in the MIDDLE_C named field.
Thanks.