Oracle SQL, getting string value between 2 points-CodePudding

I need to select a string value between two dots. (dots include)

Given abc.musadeneme.dce I need .musadeneme.

I need your help, thanks.

CodePudding user response：

Given your exact specs, this solution using REGEXP_SUBSTR will do it. Match a literal dot, followed by all characters up to and including the next literal dot. Note the 'WITH' clause just defines the set of test data, like a temp table in this case.

WITH tbl(str) AS (
  SELECT 'abc.musadeneme.dce' FROM dual
)
SELECT REGEXP_SUBSTR(str, '(\..*?\.)') AS middle_string
from tbl;


MIDDLE_STRING
-------------
.musadeneme. 
1 row selected.

If no match is found, REGXP_SUBSTR returns NULL. If there are more dots, this only returns the first substring surrounded by dots since the question mark makes the match non-greedy (stop at the first encountered match).

CodePudding user response：

You can use SUBSTR and INSTR (which is much faster than regular expressions):

SELECT SUBSTR(
         value,
         INSTR(value, '.', 1, 1),
         INSTR(value, '.', 1, 2)   1 - INSTR(value, '.', 1, 1)
       ) AS match
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'abc.musadeneme.dce' FROM DUAL;

Outputs:

MATCH
.musadeneme.

fiddle

CodePudding user response：

Using instr and substr functions we can achieve this.

SELECT
    'ab.welcome.bye'                                                               AS inp,
    substr('ab.welcome.bye',
           instr('ab.welcome.bye', '.', 1)   1,
           (instr('ab.welcome.bye',
                  '.',
                  instr('ab.welcome.bye', '.', 1)   1)) - instr('ab.welcome.bye', '.', 1) - 1) AS outp
FROM
    dual;