How to use 'REGEXP_SUBSTR' in oracle inside java code to build a dynamic sql query using S-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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