DB2 Regexp_Like in Where clause using a parameter

84 Views Asked by At

Our site just switched ERP and the new one is based on db2. In Oracle with our past ERP I could use regexp_like(last_name, || '^(' || replace(replace('&LastName',' ',''),',','|') || ')' ). If the parameter &LastName was left blank, every row would be returned.

select *
FROM  employee e
where regexp_like(last_name, 
|| '^(' || replace(replace('&LastName',' ',''),',','|') || ')' )

This allowed the user to enter A,G and the result would be all emps whose last name started with A or G after removing blanks and replacing commas with pipes (for OR condition).

I've tried all variations of regexp_like on db2. Doesn't like replace function inside. I can use

select *
FROM employee e
where regexp_like(last_name, '^${LastName}') 

to get names starting with param value, but if I leave it blank nothing is returned. Any one know if it's possible with db2? Also now to add replace comma with pipe if it's doable.

Thanks

I tried

regexp_like(last_name, 
|| '^( || replace(replace('${LastName}',' ',''),',','|') ||')' )

removing quoted params and 'replace'.

0

There are 0 best solutions below