0

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

data_henrik
  • 16,724
  • 2
  • 28
  • 49
Tim R
  • 1
  • 1
  • I assume you are on Db2 LUW, but which version? There are different "standards" for regular expressions. You might want to check the https://www.ibm.com/docs/en/db2/11.5?topic=sql-regular-expression-control-characters – data_henrik Jul 01 '23 at 09:37
  • There is a concat operator || that shouldn't be there after `regexp_like(last_name,`, but maybe it's a typo. I get the same results when I replace &LastName with some values. See [db2luw 11.1](https://dbfiddle.uk/kWNMofEh) and [oracle 21c](https://dbfiddle.uk/0ESqpH4z). Same with DB2 for I. Isn't there a problem with how &LastName is replaced before the query is sent to DB2 ? – nfgl Jul 01 '23 at 10:01
  • You must use parameter marker correctly depending on the application environment you use. Where do you run your statement from? Java, python, c, sql routine / function? – Mark Barinstein Jul 01 '23 at 11:26
  • I've used this for years with Oracle and then build reports (Crystal Rpts) using the SQLs. The latter allowed me to enter 'Jones, Smith' at the prompt and I'd get all records with those last names. I used this mainly for financial reporting so '2,3' would give me all funds starting with 2 or 3. I am now using DB2 v11.1.4.7 LUX Fixpack_num 7. I'm new to db2 as well. – Tim R Jul 03 '23 at 17:36

0 Answers0