REGEXP_LIKE QUERY IN ORACLE DB

Question

I need to currently match 00000012345 and 12345 in my DB search query. I am currently using following query:

SELECT * 
FROM BPP.CHECK_REGISTER 
WHERE CHECK_NO like CONCAT('%',:checkNum)

for searching but here % can mean any character other than 0 hence I have replaced this query with following:

SELECT * 
FROM BPP.CHECK_REGISTER 
WHERE REGEXP_LIKE (CHECK_NO,'^(0*12345)$')

but in this query I don't want to mention 12345 but mention it as a user entered parameter like the first query :checkNum

How do I rephrase the REGEXP_LIKE condition with only 2 arguments with user input :checkNum as oracle db allows only a maximum of 2 arguments. (another problem)

user330315 · Accepted Answer · 2018-11-14 10:56:19Z

1

You can concatenate the parameter:

SELECT * 
FROM BPP.CHECK_REGISTER 
WHERE REGEXP_LIKE (CHECK_NO,'^(0*'||:checkNum||')$');

Alternatively add the regex part to the user entered value (in your application code) before passing it to the query.

answered Nov 14, 2018 at 10:56

user330315

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

1 Comment

Prathima Seethalam Over a year ago

how do we use the same query from JPA ?? searchCriteria.append(" and REGEXP_LIKE (e.checkNumber,'^(0*||:checkNum||)$')"); this is not a defined standard in JPA .. how do i rephrase this??

Collectives™ on Stack Overflow

REGEXP_LIKE QUERY IN ORACLE DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related