I want to extract text before second dot(.) using regexp function from string like :

Question

I want to extract text before second dot(.) from string like:

Input - XYZ.ABC.MNO.CZ 
Output- XYZ.ABC
Input - AWQ.QA
Output- AWQ.QA

Answer 1

Looks like you want anything except dot, then dot, then anything except dot:

with t (v) as (
  select 'XYZ.ABC.MNO.CZ' from dual union all
  select 'AWQ.QA' from dual
)
select regexp_substr(v,'[^\.]+\.[^\.]+') from t;

Answer 2

Using SUBSTR + INSTR combination (which might perform better than regular expressions on large data sets):

SQL> with test (col) as
  2    (select 'XYZ.ABC.MNO.CZ' from dual union all
  3     select 'AWQ.QA' from dual
  4    )
  5  select col,
  6         substr(col, 1, case when instr(col, '.', 1, 2) = 0 then length(col)
  7                             else instr(col, '.', 1, 2) - 1
  8                        end
  9               ) result
 10  from test;

COL            RESULT
-------------- --------------
XYZ.ABC.MNO.CZ XYZ.ABC
AWQ.QA         AWQ.QA

SQL>

Answer 3

This regexp handles cases where an element of the dot-delimited string is NULL. Basically match anything, a literal dot, then anything where followed by a literal dot or the end of the string. Return the first group. Be advised REGEXP_SUBSTR will return NULL if a match is not found (REGEXP_REPLACE returns the original string).

See this post for more info on why using the REGEX form '[^.]+' does not always work as expected for parsing strings.

WITH T (ID, v) AS (
  SELECT 1, 'XYZ.ABC.MNO.CZ' FROM dual UNION ALL
  SELECT 2, '.ABC.MNO.CZ' FROM dual UNION ALL
  SELECT 3, 'XYZ..MNO.CZ' FROM dual UNION ALL    
  SELECT 4, 'AWQ.QA' FROM dual
)
SELECT ID, REGEXP_SUBSTR(v,'(.*?\..*?)(\.|$)', 1, 1, NULL, 1) substring
FROM T
ORDER BY ID;


        ID SUBSTRING     
---------- --------------
         1 XYZ.ABC       
         2 .ABC          
         3 XYZ.          
         4 AWQ.QA        

4 rows selected.

Always test with unexpected conditions in your data.