CODEWITHSUNDEEP
regexoracle11goracle12c
jhon.smith
jhon.smith

Reputation: 2043

Oracle regexp cant seem to get it right

I have some values like

CR-123456
ECR-12345
BCY-499494
134-ABC
ECW-ECR1233
CR-123344

I want to match all lines which do not start with ECR and the regex for doing so is ^((?!ECR)\w+) which seems to do what I want. But then I want to replace the matched values which do not begin with ECR and replace them with ECR and i am blanked because the following doesn't seem to work

select regexp_replace('CR-123344','^((?!ECR)\w+)','ECR') from dual

Any ideas where i have gone wrong ? I want the result to be

ECR-123456
ECR-12345
ECR-499494
ECR-ABC
ECR-ECR1233
ECR-123344

Upvotes: 0

Views: 33

Answers (3)

ForeverLearning
ForeverLearning

Reputation: 13

Looks like you are replacing characters before the '-' with ECR. Do you need to check if it does not match 'ECR' at all?

Because this will give you what you want, will it not?

select regexp_replace('CR-123344','(.*)-','ECR-') from dual;

Upvotes: 0

Brian Leach
Brian Leach

Reputation: 2101

I would use substring and instr to replace everything before the dash, but here is your answer using regexp:

 WITH aset
     AS (SELECT 'CR-123456' a
           FROM DUAL
         UNION ALL
         SELECT 'BCY-12345' a
           FROM DUAL
         UNION ALL
         SELECT 'ECR-499494' a
           FROM DUAL
         UNION ALL
         SELECT '134-ABC' a
           FROM DUAL
         UNION ALL
         SELECT 'ECW-ECR1233' a
           FROM DUAL
         UNION ALL
         SELECT 'CR-123344'
           FROM DUAL)
SELECT a, regexp_replace(a, '^([^-]*)','ECR') b
  FROM aset;

Results in

    A,B
CR-123456,ECR-123456
BCY-12345,ECR-12345
ECR-499494,ECR-499494
134-ABC,ECR-ABC
ECW-ECR1233,ECR-ECR1233
CR-123344,ECR-123344

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522161

You don't absolutely need to use regex here, you can just use Oracle's base string functions.

SELECT
    'ECR-' || SUBSTR(col,
                     INSTR(col, '-') + 1,
                     LENGTH(col) - INSTR(col, '-')) AS new_col
FROM yourTable
WHERE col NOT LIKE 'ECR-%'

The advantage of this approach is that it might run faster than a regex. The disadvantage is that the code is a bit less tidy, but if you understand how it works then this is the most important thing.

Upvotes: 1

Related Questions