CODEWITHSUNDEEP
sqlregexoracle-database
Aathira
Aathira

Reputation: 793

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

Upvotes: 0

Views: 421

Answers (2)

Popeye
Popeye

Reputation: 35900

You can simply use INSTR and SUBSTR as following:

SQL> SELECT
  2      SUBSTR('AA.BBB.12.11.ccCC', 1, INSTR('AA.BBB.12.11.ccCC', '.', -2, 2) - 1) AS RESULT
  3  FROM DUAL;

RESULT
---------
AA.BBB.12

SQL>

-- Update --

For the question asked in the comment, use the following query:

SQL> SELECT
  2      SUBSTR('AA.BB.CC.DD', 1, INSTR('AA.BB.CC.DD', '.', 1, 3) - 1) AS RESULT
  3  FROM DUAL;

RESULT
--------
AA.BB.CC

SQL>

Cheers!!

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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

Upvotes: 3

Related Questions