Skip to content
Advertisement

Convert from sql query condition to REGEXP_LIKE

I am converting an SQL query condition like:

where req_number like '169___[3,4,5,6]10%' OR req_number like '169___[3,4,5,6]51%'

I tried to execute query in Oracle using condition belove but no result return:

where (REGEXP_LIKE (req_number, '169___[3456]10$') OR REGEXP_LIKE (req_number, '169___[3456]51$'))

Result look like for example 169011310 so _ are numeric characters

Advertisement

Answer

The $ in regex means end of string, you need to remove it because % in TSQL means any text, any zero or more chars, and REGEXP_LIKE does not require a full string match.

As _ in TSQL means any single char you can replace each _ with . in regex.

The 169 must be matched at the start of string because TSQL like patterns must always match the entire record, so you need to add ^ at the beginning of the regex.

You can use

where (REGEXP_LIKE (req_number, '^169.{3}[3456]10') OR REGEXP_LIKE (req_number, '^169.{3}[3456]51'))

You can write .{3} as ..., however, it is easier to scale such a regex later, using the limiting quantifier {n} (n times).

Also, you may change [3456] into [3-6] since the digits are consecutive here.

And optimizing it further, since the expresions only differ in the last number, you may just use alternation:

where REGEXP_LIKE (req_number, '^169.{3}[3-6](10|51)')

where (10|51) matches either 10 or 51.

8 People found this is helpful
Advertisement