How to use 'REGEXP_SUBSTR' in oracle inside java code to build a dynamic sql query using StringBuilder?

Question

I am building a dynamic query in my Java code using StringBuilder. I need to append a SELECT sub query with REGEXP_SUBSTR expression inside an IN clause. Below is what I did.

StringBuilder criteria = new StringBuilder();

criteria.append(" IN (SELECT REGEXP_SUBSTR((select tableA.mdn_list from tableA where tableA.id = '1'), '[^,]+', 1, level)  FROM dual CONNECT BY REGEXP_SUBSTR((select tableA.mdn_list from tableA where tableA.id = '1'), '[^,]+', 1, level) IS NOT NULL))

But this gives an error in the terminal.

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: BY near line 3, column 19 .....

How to add REGEXP_SUBSTR in this code?

This question relates with the question here (sample data): How to add comma-separated string value to an 'IN' clause as an item list in SQL using sub select query?

Some sample data along with an explanation of what the query is supposed to be doing would be helpful here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 7, 2022 at 6:16
@Tim, it splits a comma-separated list of values into rows. Roshi, as far as Oracle is concerned, that query works OK so I presume that it is something else (Stringbuilder?) that doesn't understand the CONNECT BY clause (as it complains about "BY"). I don't know anything about Java so - just saying. Can't help much. — Littlefoot
– Littlefoot, Commented Dec 7, 2022 at 6:49
@Littlefoot Well if the OP is trying to run this as JPQL it won't work, as it is Oracle specific. It might run as a native query though. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 7, 2022 at 6:53
@Littlefoot yes the query works fine with Oracle. But the issue comes when I use it in the code. — RoshiDil
– RoshiDil, Commented Dec 7, 2022 at 7:08

MT0 · Accepted Answer · 2022-12-07 11:19:02Z

0

You do not need to split the string to find out if a value is in a delimited list.

It will be much faster to check if the complete term (with surrounding delimiter characters) is a sub-string of your delimited list (again, with surrounding delimiter characters):

WHERE (SELECT ',' || mdn_list || ',' FROM tableA WHERE id = '1')
        LIKE  '%,' || your_column || ',%'

fiddle

However, your error:

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: BY near line 3, column 19 .....

Should not be caused by your code as your code is syntactically valid (as far as Oracle is concerned). But you can remove the use of CONNECT BY by filtering on sub-string matches.

edited Dec 7, 2022 at 11:19

answered Dec 7, 2022 at 9:15

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RoshiDil Over a year ago

how is removing the use of CONNECT BY by filtering on sub-string matches possible? Any examples?

MT0 Over a year ago

@RoshiDil The example is in the first part of my answer using LIKE to match the sub-string. I've added a fiddle with a complete example.

p3consulting · Accepted Answer · 2022-12-09 20:41:28Z

0

"org.hibernate.hql.ast.QuerySyntaxException": this means you pass a native SQL fragment to the HQL parser... so even if the SQL is correct...

answered Dec 9, 2022 at 20:41

p3consulting

5,4312 gold badges14 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to use 'REGEXP_SUBSTR' in oracle inside java code to build a dynamic sql query using StringBuilder?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related