CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
Venkatesh R
Venkatesh R

Reputation: 530

Getting string with second occurrence using regexp_substr in oracle PLSQL

Input value:

1-> 0055030_1-1-202201060155
2-> 0055040_1-8-202201050155-0501

Expected output for the corresponding input:

1-> 0055030_1-1
2-> 0055040_1-8

The below SQL queries do not return the expected output

select Regexp_substr('0055030_1-1-202201060155', '[^-^]+', 1, 1) from dual;

returns

0055030_1

select Regexp_substr('0055030_1-1-202201060155', '[^-^]+', 1, 2) from dual;

returns

1

Do I need to go substring or any pattern that will provide the required result?

Upvotes: 0

Views: 916

Answers (2)

Katerina Fulneckova
Katerina Fulneckova

Reputation: 1

I would use the 'search for pattern version' of this function: select Regexp_substr('0055030_1-1-202201060155', '(\d*)_(\d)-(\d)') from dual;

The pattern is: (\d*) multiple digits followed by underscore, (\d) one digit, dash and one digit. Should be replaced with '(\d*)_(\d*)-(\d*)' if the numbers between dashes have more than one digit.

Upvotes: 0

EJ Egyed
EJ Egyed

Reputation: 6064

You do not need to use regex and can instead use SUBSTR and INSTR

WITH
    sample_data (test_str)
    AS
        (SELECT '0055030_1-1-202201060155' FROM DUAL
         UNION ALL
         SELECT '0055040_1-8-202201050155-0501' FROM DUAL)
SELECT test_str, substr(test_str, 1, instr(test_str,'-', 1, 2) - 1) as return_val
  FROM sample_data;


                        TEST_STR     RETURN_VAL
________________________________ ______________
0055030_1-1-202201060155         0055030_1-1
0055040_1-8-202201050155-0501    0055040_1-8

Upvotes: 1

Related Questions