REGEXP_SUBSTR is inconsistent with extracting digits before a comma/decimal point-CodePudding

my regex looks like this:

select REGEXP_SUBSTR(my_column,'[^,] ',1,1) from myTable;

this returns the results just fine:

regex	value
11	11,2
5	5,2
11	11,4
6	6,6

however when a value starts with a 0 its returns the following

regex	value
1	0,1

How can I select the 0 instead of the 1?

CodePudding user response：

If you had a string with '0,1' your query would return 0, not 1.

What you're seeing suggests your column is actually a number rather than a string, your NLS_NUMERIC_CHARACTERS setting has a comma as the decimal separator, and you using implicit conversion to convert the number to a string then applying your regular expression to that.

So rather than doing any string manipulation, just truncate the values to get the integer part:

select TRUNC(my_column) from myTable;

db<>fiddle

CodePudding user response：

Since you want the leading digits before the comma, you should be using ^[^,] :

SELECT REGEXP_SUBSTR(my_column, '^[^,] ', 1, 1) FROM myTable;

CodePudding user response：

Do you really need regular expressions here? It seems you want to return values that precede comma character. So, if all values follow the same pattern you posted in sample data, then substr instr do the job:

Sample data:

SQL> with test (col) as
  2    (select '11,2' from dual union all
  3     select '5,2'  from dual union all
  4     select '11,4' from dual union all
  5     select '6,6'  from dual union all
  6     select '0,1'  from dual
  7    )

Query begins here:

  8  select col, substr(col, 1, instr(col, ',') - 1) result
  9  from test;

COL  RESU
---- ----
11,2 11
5,2  5
11,4 11
6,6  6
0,1  0

SQL>

CodePudding user response：

If your values are supposed to represent numbers, you can just use TRUNC as long as your numeric separated characters are set properly for your session.

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ", ";

WITH
    sample_data (my_column)
    AS
        (SELECT TO_NUMBER ('11,2') FROM DUAL
         UNION ALL
         SELECT TO_NUMBER ('5,2') FROM DUAL
         UNION ALL
         SELECT TO_NUMBER ('11,4') FROM DUAL
         UNION ALL
         SELECT TO_NUMBER ('6,6') FROM DUAL
         UNION ALL
         SELECT TO_NUMBER ('0,1') FROM DUAL)
SELECT trunc(my_column)
  FROM sample_data;