TEXT column: Argument data type text is invalid for argument 1 of lower function 2
£10-20 GBP
已完成
已发布超过 3 年前
£10-20 GBP
货到付款
I currently have a repo method which is acting as a filter with pagination:
Repo:
@Query("SELECT e FROM DeTest e " +
"WHERE [login to view URL] = '9999-12-31 00:00:00.000' " +
"AND (:name is null OR LOWER ([login to view URL]) LIKE LOWER ('%' + :name+ '%')) " +
"AND (:description is null OR LOWER ([login to view URL]) LIKE LOWER ('%' + :description + '%')) " +
"AND (:theProblem is null OR LOWER ([login to view URL] ) LIKE LOWER ('%' + :theProblem + '%')) "
)
So as shown here, I have it so when you enter a value it will change it ensuring that both uppercase values and lowercase values are displayed. However, when adding LOWER to the 'theProblem' column, it produces the error in the title:
Argument data type text is invalid for argument 1 of lower function
My datatype for that particular column is text, and therefore, I think the culprit of the issue. However, due to the db layout I need it to be a text. Therefore, is there anyway I could convert/cast it in the repo method for when it's called?
Column:
@Column(name = "the_problem", columnDefinition = "TEXT") // text rather than varchar
private String theProblem;
Thank you.
Hi, I am confident I can fix this query for you, I have often had to deal with similar issues with a TEXT column from older versions of SQL server when TEXT was the only option.
I have 11 years experience as a SQL Developer and DBA, please feel free to ask any questions.