How to convert an invalid number column to a number on HANA?

Question

I have a table with a string column. I convert this column to a number using the function TO_INTEGER(). Ist work fine. But If I Aggregate the converted column with the function SUM I got this error:

SAP DBTech JDBC: [339]: invalid number: not a valid number string ' ' at function to_int()

This is my sample SQL query:

select SUM(PARTICIPANT)
from (
select TO_INTEGER(STUDENT) as PARTICIPANT
from MyTable)

Column STUDENT is a varchar(50) in MyTable

What did I do wrong?

Thanks in advance

Answer 1

Without seeing your Column values, it looks like you're trying to convert the numeric sequence at the end of your values list to a number, and the spaces that delimit it are throwing this error. But based on the information you've given us, it could be happening on any field.

Eg:

Create table Table1 (tel_number number);
Insert into Table1 Values ('0419 853 694');

The above gives you a

invalid number