0

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)

Prathima Seethalam
  • 1

1 Answers1

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.

  • 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?? – Prathima Seethalam Nov 15 '18 at 06:59