[FIXED] Issue with concat function hibernate in hql query

Issue

I've an hql query which looks like this with dynamic values

    SELECT * from sample_view where (concat(course_id,'-',stu
    _id)) in ('11-001'));

Above query returns 4 rows , but same query with below values returns nothing

    SELECT * from sample_view where (concat(course_id,'-',stu
    _id)) in ('011-001'));

Also when I executed these below queries , both of them returns 4 rows.

SELECT * from sample_view where course_id in ('011') and stu_id in ('001');

SELECT * from sample_view where course_id in ('11') and stu_id in ('001');

How can I make concat function to return same values when input has numbers with 0 as prefix. Any help in this regard would be highly appreciated. Thanks in advance

Solution

First of all, do not use the concatenation trick at the start of your question. Use the full query with checks on both columns:

SELECT *
FROM sample_view
WHERE course_id = ? AND stu_id = ?;

If you want to disregard leading zeroes from either ID, simply strip off those zeroes before you bind the values to the statement, e.g.

String courseId = "011";
courseId = courseId.replaceFirst("^0+", "");
System.out.println(courseId); // 11
// bind course ID here to your statement

Answered By - Tim Biegeleisen
Answer Checked By - Terry (JavaFixing Volunteer)