Please help me solve the problem.
This is the table:
| ID_client | values |
|---|---|
| 1 | 0,46 |
| 2 | 25% |
| 3 | No information |
| 4 | Twenty two |
| 5 | 12.2 |
| 6 | 365% |
| 7 | 54 |
I need get numbers from string as percentages.
This is the result I need to get from the query:
| IDs | values |
|---|---|
| 1 | 0,46 |
| 2 | 25 |
| 3 | null |
| 4 | null |
| 5 | 12,2 |
| 6 | 365 |
| 7 | 54 |
I tried some regexp that I have found here but those did not work correctly.
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.
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:
| VALUE | NUMBER_VALUE |
|---|---|
| 0,46 | .46 |
| 25% | 25 |
| No information | null |
| Twenty two | null |
| 12.2 | 12.2 |
| 365% | 365 |
| 54 | 54 |
