Reputation: 530
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
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
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