CODEWITHSUNDEEP
sqlregexoracle-database
Ianthe
Ianthe

Reputation: 5709

Oracle SQL : Extracting data by splitting through delimiter in the query

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 -

Upvotes: 0

Views: 320

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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

Upvotes: 0

Utsav
Utsav

Reputation: 8093

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.

Upvotes: 1

Related Questions