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
CodePudding user response：
The COLLATE operator determines the collation for an expression
That means that you have to apply it per column expression in your case