CODEWITHSUNDEEP
springdatabasehibernateconcatenationhql
Rachanaa
Rachanaa

Reputation: 53

Issue with concat function hibernate in hql query

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

Upvotes: 0

Views: 572

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

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

Upvotes: 1

Related Questions