Error when using REGEXP_LIKE with DB2

Question

I'm attempting what I thought was a pretty straight-forward SQL statement. I'm trying to use a REGEX expression to only grab records that have a name consisting of only 3 numbers.

However, I'm getting an error and I can't figure out why. Because it's DB2, there aren't nearly as many examples to draw from (as there would be with Postgres, for example), so I'm stuck.

Can anyone see what's wrong?

SELECT
    ia.ID
   ,ia.DESCRIPTION
FROM INVENTORY.ACTIVITIES ia
WHERE
     REGEXP_LIKE(ia.NAME, '[0-9]{3}')

Error:

[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token ")" was found following "(ia.NAME,'[0-9]{3}')". Expected tokens may include: "<interval_qualifier>". SQLSTATE=42601.

According to [the manual](https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/c0000767.html?pos=2) there is no `regexp_like` in DB2 — , Oct 30 '17 at 18:20
It seems this was added in the most recent release (11.1): http://www.idug.org/p/bl/et/blogaid=605 — , Oct 30 '17 at 18:21

score 1 · Answer 1 · answered Oct 30 '17 at 18:18

Your code should compile. It doesn't do what you want though. You need markers for the beginning and end of the string:

SELECT ia.ID, ia.DESCRIPTION
FROM INVENTORY.ACTIVITIES ia
WHERE REGEXP_LIKE(ia.NAME, '^[0-9]{3}$')

My best guess for your error is a hidden character.

Error when using REGEXP_LIKE with DB2

1 Answers1