Skip to content
Advertisement

SQL and Oracle query to extract every thing before last two periods

I need to extract every thing before last two periods

eg.

Input: AA.BBB.12.11.cc

Output: AA.BBB.12

Following is the sample query I am using but that returns only the characters before first period, but that is not I needed.

SELECT REGEXP_SUBSTR(t.column,'[^.]+',1,1)
 AS output
 FROM MY_Table  t where t.column is not null and rownum=1

Advertisement

Answer

I would use REGEXP_REPLACE here:

SELECT REGEXP_REPLACE(t.column, '.[^.]+.[^.]+$', '')
FROM MY_table
WHERE t.column IS NOT NULL AND rownum = 1;

The regex pattern .[^.]+.[^.]+$ will match starting with the second to last dot, all content until the end (including also the last dot).

10 People found this is helpful
Advertisement