I have a string format of MID: ABC-123212-2 - SID: 21354 in a column.
Expected result: ABC-123212-2 and 21354.
Have tried
SELECT REGEXP_SUBSTR('MID: ABC-123212-2 - SID: 21354', '\d[0-9-]*', 1, 1) FROM DUAL;
SELECT REGEXP_SUBSTR('MID: ABC-123212-2 - SID: 21354', '\d[0-9-]*', 1, 2) FROM DUAL;
But the result is only getting the number.
How can I include the letters also, splitting the data by : and middle -
This will give you what you want and it can handle any white spaces in between fields and separators.
To see how this regex works, see regex101 Demo
To see how this query works, see dbfiddle demo
select
regexp_replace(col1,'MID:\s*(\S+).*SID:\s*(\S+).*','\1') as field1
,regexp_replace(col1,'MID:\s*(\S+).*SID:\s*(\S+).*','\2') as field2
from
(select 'MID: ABC-123212-2 - SID: 21354' as col1 from dual);
Output
FIELD1 FIELD2
ABC-123212-2 21354
Explanation:
Regex MID:\s*(\S+).*SID:\s*(\S+).* uses 2 capturing groups, enclosed in braces(). So after MID: we will capture first group, and after SID:, we will capture next.
Now regexp_replace can return any capturing group from regex. So \1 and \2 are use in 2 result set.
You don't need REGEXP_SUBSTR() for this, you can just use basic string functions:
SELECT
SUBSTR(col, 6, INSTR(col, '- SID:') - 7) AS term1,
SUBSTR(col, INSTR(col, 'SID:') + 5) AS term2
FROM yourTable
In most cases, I would expect a solution involving the base string functions to outperform a regex based solution. This is not to say that regex does not have a time and place, but for something as simple as your extraction I might not use it.
As usual, I couldn't get an Oracle demo working in either Rextester or SQLFiddle, but follow the link below for a MySQL demo. The string functions INSTR() and SUBSTR() behave almost the same in Oracle and MySQL, so the logic is still valid.
dual and couple of other stuff doesn't work here, and you have to create a table and insert data to it before testing the query. But a good alternative in case nothing else works.
