substring, after last occurrence of character?

Question

I have a column named phone_number and I wanted to query this column to get the the string right of the last occurrence of '.' for all kinds of numbers in one single sql query.

example:

515.123.1277

011.44.1345.629268

I need to get 1277 and 629268 respectively.

I have this so far: select phone_number, case when length(phone_number) <= 12 then substr(phone_number,-4) else substr (phone_number, -6) end from employees;

This works for this example, but I want it for all kinds of formats.

Answer 1

It should be as easy as this regex:

SELECT phone_number, REGEXP_SUBSTR(phone_number, '[^.]*$')
  FROM employees;

With the end anchor $ it should get everything that is not a . character after the final .. If the last character is . then it will return NULL.

Answer 2

Search for a pattern including the period, [.] with digits, \d, followed by the end of the string, $.

Associate the digits with a character group by placing the pattern, \d, in parenthesis (see below). This is referenced with the subexpr parameter, 1 (last parameter).

Here is the solution:

SCOTT@dev> list

  1  WITH t AS
  2    ( SELECT '414.352.3100' p_number FROM dual
  3    UNION ALL
  4    SELECT '515.123.1277' FROM dual
  5    UNION ALL
  6    SELECT '011.44.1345.629268' FROM dual
  7    )
  8* SELECT regexp_substr(t.p_number, '[.](\d+)$', 1, 1, NULL, 1) end_num FROM t
SCOTT@dev> /

END_NUM
========================================================================
3100
1277
629268