I have an a spring boot project with a nativeQuery sql query that is executed by front end code each time a user types in a letter in a certain field. In the query, user input serves as a parameter to two database fields. I currently have it like this:
SELECT * FROM EMPLOYEE WHERE ID LIKE '%input%' OR NAME LIKE '%input%' collate binary_ci ORDER BY NAME ASC
I am testing it in postman, and results seem to indicate the collation above applies to both fields, but I want to make sure it applies to both fields. I've searched but did not find posts or documentation that answers my question.
Does the way I have it suffice, or do I need it after each field name like below?
SELECT * FROM EMPLOYEE WHERE ID LIKE '%input%' collate binary_ci OR NAME LIKE '%input%' collate binary_ci ORDER BY NAME ASC
The COLLATE operator determines the collation for an expression
That means that you have to apply it per column expression in your case