IBATIS query where column contains exact String

Question

I'm trying to create a query that will return everything containing a exact string in a specific column. It should return x = "create query" when I search for create or query, but not return anything when I search for reate or uer.

I need the equivalent of:

select * 
from table 
where column_name ~* \ycreate\y

and where I will replace create with a passed parameter. I see this works if I hardcode the value, but I was not able to pass it from a method, this is the code I tried:

@Select({SELECT * from table where column_name ~* '\\y' || #{valuePassedFromMethod} || '\\y'"})
List<String> methodName(@Param("valuePassedFromMethod") String valuePassedFromMethod);

I don't have a lot of experience and I was not able to find the problem, I would appreciate if somebody will help me fix this.

Answer 1

The problem here is that the expression column_name ~* '\\y' || #{valuePassedFromMethod} || '\\y' is being evaluated from left to right, since all operators have the same precedence. You can see here that all the operators involved fall in the "all other native and user-defined operators" category, and thus they have the same precedence.

column_name ~* '\\y' returns true, and the subsequent concatenations return a text. So your query ends up looking like SELECT * FROM unl_unit WHERE 'truecreate\y', assuming you are looking for the string create. You can see now how the WHERE clause in fact complains about receiving a text instead of a boolean. You can simply enclose the search pattern with parentheses to avoid this.

@Select({SELECT * from table where column_name ~* '(\\y' || #{valuePassedFromMethod} || '\\y')"})
List<String> methodName(@Param("valuePassedFromMethod") String valuePassedFromMethod);

That being said, I don't think the regex you are using actually reflects the search you are trying to achieve. The \y means start/end of string, so you are actually searching for the exact string 'create'. If you want to search for strings containing create, you should do something like:

SELECT * FROM unl_unit WHERE unlun_name like '%' || 'create' || '%';

And the method would be:

@Select({SELECT * from table where column_name LIKE '%' || #{valuePassedFromMethod} || '%'"})
List<String> methodName(@Param("valuePassedFromMethod") String valuePassedFromMethod);

Notice how you don't even need the parentheses here, since the LIKE operator has a lower precedence than the concatenation operators.