Converting SQL query into regex

Question

can you provide me with confirmation whether I correctly converted following SQL Queries into regex expression?

1) SQL:

CLASSIFICATION_CODE not like '24%'

REGEX:

CLASSIFICATION_CODE <> '([^2]|2[^4])'

2) SQL:

TYPE_CODE NOT LIKE 'DKRI%' OR TYPE_CODE  NOT LIKE 'KRI%' OR TYPE_CODE  <> '355' OR TYPE_CODE  <> '303'

REGEX:

TYPE_CODE <> '([^DK3]|D[^K]|DK[^R]|DKR[^I]|K[^R]|KR[^I]|3[^50]|30[^3]|35[^5]'

Thanks in advance,

a.

Which language are you using to convert? Also, your regex patterns are incorrect: NOT LIKE 'DKRI%' would convert to ^DKRI and <> 355 would convert to ^355 — ctwheels
– ctwheels, Commented Mar 7, 2018 at 15:51
Well, to be precise - I do use SQL Extensions toolkit for Netezza which is pretty much the same as Oracle one. — Przemek
– Przemek, Commented Mar 7, 2018 at 16:12

Gordon Linoff · Accepted Answer · 2018-03-07 15:58:01Z

1

No. Regular expressions match anywhere, but like is always the full string. So you need to anchor the regular expressions:

regexp_like(CLASSIFICATION_CODE, '^([^2]|2[^4])'

Although, I would write this as:

not regexp_like(CLASSIFICATION_CODE, '^24')

Your second is much simpler:

CLASSIFICATION_CODE IS NOT NULL

or:

regexp_like(CLASSIFICATION_CODE, '.')

I could speculate that you really intend AND between the conditions, but with OR, any string is going to match.

answered Mar 7, 2018 at 15:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1