I need to select a string value between two dots. (dots include)
Given abc.musadeneme.dce I need .musadeneme.
I need your help, thanks.
-
Does this answer your question? Oracle SQL: Extracting text between two charactersOldProgrammer– OldProgrammer2022-10-06 18:12:40 +00:00Commented Oct 6, 2022 at 18:12
-
does the source data always include both dots? What if one is missing. What if there are 3?Hogan– Hogan2022-10-06 18:26:33 +00:00Commented Oct 6, 2022 at 18:26
Add a comment
|
3 Answers
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).
Comments
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. |
1 Comment
Musa Keskin
Thank a lot. This usage is so fit to me.
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;
Comments
