Oracle REGEXP_REPLACE function to find decimal and special characters-CodePudding

I am working with table data that contains strings with decimal and back-slash like below:

info
1/2.2.2
2/1.1.1
3/1.1.11

I need to use a regular expression to replace the data like below:

info
1/2.2
2/1.1
3/1.1

CodePudding user response：

Don't use a (slow) regular expression, use simple (faster) string functions instead:

SELECT info,
       CASE
       WHEN INSTR(info, '.', 1, 2) > 0
       THEN SUBSTR(info, 1, INSTR(info, '.', 1, 2) - 1)
       ELSE info
       END AS part
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (info) AS
SELECT '1/2.2.2' FROM DUAL UNION ALL
SELECT '2/1.1.1' FROM DUAL UNION ALL
SELECT '3/1.1.11' FROM DUAL UNION ALL
SELECT '3/1.1' FROM DUAL;

Outputs:

INFO	PART
1/2.2.2	1/2.2
2/1.1.1	2/1.1
3/1.1.11	3/1.1
3/1.1	3/1.1

If you want to update the table then:

UPDATE table_name
SET   info = SUBSTR(info, 1, INSTR(info, '.', 1, 2) - 1)
WHERE INSTR(info, '.', 1, 2) > 0

fiddle