hanahana-sql-script

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

I have a table with a string column. I convert this column to a number using the function TO_INTEGER(). It works 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

Solution

  • 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