How to Format phone number like 999-999-9999×1234567890 to 999-999-9999×12345 in oracle sql

If there is x in the given phone number, then after truncating there won’t be more than 5 digits after x.

If there is no x in the phone number, then after truncating it will same

Here is the possible situations

999-999-9999x1234567890  => 999-999-9999x12345
123-456-9999x123         => 123-456-9999x123   (no change)
999-999-9999             => 999-999-9999   (no change)
123456789                => 123456789   (no change)
999-1234567892           => 999-1234567892   (no change)

How can I achieve this in oracle SQL using regexp_substr or any other methods

Answer

You can use (quick) simple string functions:

SELECT value,
       CASE INSTR(value, 'x')
       WHEN 0
       THEN value
       ELSE SUBSTR(value, 1, INSTR(value, 'x') + 5)
       END AS shortened_value
FROM   table_name;

or (slower) regular expressions:

SELECT value,
       REGEXP_REPLACE(value, '(x.{5}).+$', '1')
         AS shortened_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '99-999-9999x1234567890' FROM DUAL UNION ALL
SELECT '123-456-9999x123' FROM DUAL UNION ALL
SELECT '999-999-9999' FROM DUAL UNION ALL
SELECT '123456789' FROM DUAL UNION ALL
SELECT '999-1234567892' FROM DUAL;

Both output:

VALUE	SHORTENED_VALUE
99-999-9999×1234567890	99-999-9999×12345
123-456-9999×123	123-456-9999×123
999-999-9999	999-999-9999
123456789	123456789
999-1234567892	999-1234567892

db<>fiddle here