0

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

Given abc.musadeneme.dce I need .musadeneme.

I need your help, thanks.

APC
  • 144,005
  • 19
  • 170
  • 281
Musa Keskin
  • 15
  • 4
  • Does this answer your question? [Oracle SQL: Extracting text between two characters](https://stackoverflow.com/questions/50004535/oracle-sql-extracting-text-between-two-characters) – OldProgrammer Oct 06 '22 at 18:12
  • does the source data always include both dots? What if one is missing. What if there are 3? – Hogan Oct 06 '22 at 18:26

3 Answers3

2

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

Gary_W
  • 9,933
  • 1
  • 22
  • 40
1

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

MT0
  • 143,790
  • 11
  • 59
  • 117
0

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
Peeyush Jain
  • 33
  • 4