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
CodePudding user response:
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-9999x1234567890 99-999-9999x12345 123-456-9999x123 123-456-9999x123 999-999-9999 999-999-9999 123456789 123456789 999-1234567892 999-1234567892
db<>fiddle here