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)

Answer 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.