CODEWITHSUNDEEP
regexoracle-databasestored-procedures
HEEN
HEEN

Reputation: 4727

Stored procedure for data excluding is not working as expected in oracle

I have written a query where I want to exclude the data for which values comes as _900. Below is the query for the same.

SELECT
TO_CHAR(TRIM(RJ_SPAN_ID)) AS SPAN_ID,TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE,RJ_INTRACITY_LINK_ID
FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
--FROM APP_FTTX.SPAN_2@SAT
WHERE
LENGTH(TRIM(RJ_SPAN_ID)) = 21
--AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 'SP(N|Q|R|S)*.+_(BU|MP)$','i')
--AND (NOT REGEXP_LIKE(TRIM(RJ_SPAN_ID), '(_U)$|(/)$','i')
--AND REGEXP_LIKE(RJ_INTRACITY_LINK_ID, '(%*_9%)','i')--)
AND INVENTORY_STATUS_CODE = 'IPL'
AND RJ_MAINTENANCE_ZONE_CODE = 'INORBNPN01'
and RJ_SPAN_ID = 'ORKPRKORKONASPR001_BU';

I tried all the commented REGEXP but it's not working.

Also, with the above query below is the screenshot for the output which I am getting.

IMG

Upvotes: 0

Views: 40

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

Why not LIKE?

SQL> with test (col) as
  2    (select '900'       from dual union all  --> doesn't contain _900
  3     select '123_900AB' from dual union all  --> contains _900
  4     select '_900'      from dual union all  --> contains _900
  5     select 'ab900cd'   from dual            --> doesn't contain _900
  6    )
  7  select *
  8  from test
  9  where col not like '%\_900%' escape '\';

COL
---------
900
ab900cd

SQL>

Upvotes: 1

Related Questions