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.