Add a TO_NUMBER(str) SQL function

[fsbflavio]

Add an SQL function to convert a string to a number.

Also use the CsvJdbc "locale" database connection properties when converting the string, if set. It should consider, for example, (coma) for en, and . for pt-BR locales.

This could allow mathematical operations over string columns, like: SELECT SUM(TO_NUMBER(VALUE)) FROM TABLE

[simoc]

Good idea.

If you implement TO_NUMBER() yourself, then I will accept the changes into CsvJdbc.

Alternatively, if you wait "a couple of weeks", then I will have time to implement TO_NUMBER() myself.

[fsbflavio]

I would appreciate it if you could implement the TO_NUMBER function, even if it takes some time. I'm not a Java developer, so I will spend too much time until I can do it myself.

I use csvjdbc with DBeaver and being able to use TO_NUMBER to convert the default string values will be helpful.

[simoc]

For reference:

Documentation for the TO_NUMBER() SQL function in PostgreSQL is at:

https://www.postgresql.org/docs/current/functions-formatting.html

Documentation for the TO_NUMBER() SQL function in Oracle is at:

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html

Documentation for Java DecimalFormat number parsing class is at:

https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/DecimalFormat.html

[fsbflavio]

Excellent! When will this be available in releases?

[simoc]

This problem is fixed in Csvjdbc version 1.0.44.

Download it from https://sourceforge.net/projects/csvjdbc/files/ or from https://central.sonatype.com/