Example

123\.456.578.910.ABC
123\.456.578.910

Expected result

123\.456.578
123\.456.578

For the both the inputs I should get only the first 3

I tried the regexp and substring and instr but I’m not getting the results

CodePudding user response：

We can use REGEXP_SUBSTR here with a capture group:

SELECT REGEXP_SUBSTR(col, '^(\d (\.\d )*)', 1, 1, NULL, 1)
FROM yourTable;

Demo

CodePudding user response：

Traditional, substr instr combination is another option:

Sample data:

SQL> with test (col) as
  2    (select '123\.456.578.910.ABC' from dual union all
  3     select '123\.456.578.910'     from dual
  4    )

Query begins here:

  5  select col,
  6         substr(col, 1, instr(col, '.', 1, 3) - 1) result
  7  from test;

COL                  RESULT
-------------------- --------------------
123\.456.578.910.ABC 123\.456.578
123\.456.578.910     123\.456.578

SQL>

CodePudding user response：

If you value will always have at least 3 . characters then you can use:

SELECT value,
       SUBSTR(value, 1, INSTR(value, '.', 1, 3) - 1) AS expected
FROM   table_name;

If it may have fewer and you want the entire string in those cases then:

SELECT value,
       CASE INSTR(value, '.', 1, 3)
       WHEN 0
       THEN value
       ELSE SUBSTR(value, 1, INSTR(value, '.', 1, 3) - 1)
       END AS expected
FROM   table_name;

Which, for your sample data:

CREATE TABLE table_name (value) AS
SELECT '123\.456.578.910.ABC' FROM DUAL UNION ALL
SELECT '123\.456.578.910' FROM DUAL;

Both outputs:

VALUE	EXPECTED
123.456.578.910.ABC	123.456.578
123.456.578.910	123.456.578

db<>fiddle here