REGEXP_SUBSTR is inconsistent with extracting digits before a comma/decimal point [closed]

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?

Answer

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