please help me solve the problem. The table:

id_client, values

I need get numbers from string as percantages

Need next result of quarry

Tryied some regexp that i have found here but it works wrong

CodePudding user response：

A regex substring operation should at least be a good place to get started:

SELECT
    id_client,
    REGEXP_SUBSTR(values, '[0-9] ([,.][0-9] )?') AS values
FROM yourTable;

The above query would leave the values column with only numeric strings. To get an actual numeric column, you would need some kind of additional cast. Your data seems to be using both comma and dot as decimal place. You may need to do an additional replace on top of my answer above to get the floats into the right format for your locale.

CodePudding user response：

Trim the % character from the right of the string and normalise the decimal point to a single character and then use TO_NUMBER with the DEFAULT NULL ON CONVERSION ERROR, which is available from Oracle 12:

SELECT value,
       TO_NUMBER(
         REPLACE(RTRIM(value, '%'), ',', '.') DEFAULT NULL ON CONVERSION ERROR,
         '999999.99'
       ) AS number_value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (ID, value) AS
SELECT 1, '0,46' FROM DUAL UNION ALL
SELECT 2, '25%' FROM DUAL UNION ALL
SELECT 3, 'No information' FROM DUAL UNION ALL
SELECT 4, 'Twenty two' FROM DUAL UNION ALL
SELECT 5, '12.2' FROM DUAL UNION ALL
SELECT 6, '365%' FROM DUAL UNION ALL
SELECT 7, '54' FROM DUAL;

Outputs:

fiddle