Search code examples
sqloraclesubstring

substring, after last occurrence of character?

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.

Solution

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