CODEWITHSUNDEEP
sqlregexoracle-database
banana_99
banana_99

Reputation: 641

Regex negate suffix negative lookaround not working

I'm trying to set as null the rows that do end with the pattern 'NEW'.

This would be my desired output (the input would just be the first column):

           DCALIVE      DESIRED_OUTPUT
--------------------------------------
      DCA_LIVE_NEW                NULL 
     DCA_LIVE_NEWS       DCA_LIVE_NEWS
DCA_LIVE_NO_RECALL  DCA_LIVE_NO_RECALL  
 DCA_LIVE DCA_LIVE   DCA_LIVE DCA_LIVE

So far, I've tried a negative lookaround as suggested in other answers, but it does not return values for any of the rows

WITH aux ( dcalive ) AS (
    SELECT
        'DCA_LIVE_NEW'
    FROM
        dual
    UNION ALL
    SELECT
        'DCA_LIVE_NEWS'
    FROM
        dual
    UNION ALL
    SELECT
        'DCA_LIVE_NO_RECALL'
    FROM
        dual
    UNION ALL
    SELECT
        'DCA_LIVE'
    FROM
        dual
)
SELECT
    a.*,
    decode(regexp_substr(dcalive, '(?!NEW)$'), NULL, NULL, dcalive) desired_output
FROM
    aux a;

Upvotes: 1

Views: 39

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

One option is keeping a negative start_position argument in order to look the characters backwards along with a conditional statement such as

SELECT dcalive, DECODE( SUBSTR(dcalive,-3), 'NEW', NULL, dcalive ) AS output
  FROM aux

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You could use a case expression:

select (case when dcalive not like '%NEW' then dcalive end) as desired_output

Regular expressions aren't needed for this. But you could express this as a regular expression:

select (case when not regexp_like(dcalive, 'NEW$)' then dcalive end) as desired_output

Upvotes: 2

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

You can use

([^N].{2}|.[^E].|..[^W]|^.{0,2})$

See the regex demo. Details:

  • ([^N].{2}|.[^E].|..[^W]|^.{0,2}) - either of
    • [^N].{2} - any non-N and two chars
    • | - or
    • .[^E]. - any char, non-E, any char
    • | - or
    • ..[^W] - any two chars and a non-W
    • | - or
    • ^.{0,2} - start of stirng and any zero, one or two chars
  • $ - end of string.

Upvotes: 1

Related Questions