Question: 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 `-`

Answer: 
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](https://regex101.com/r/33JcHm/1)**

To see how this query works, see [dbfiddle demo](http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=85eb069ca97977053dab56929db5aa32) 

```
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.

[Demo
====](http://rextester.com/XUYL92435)