Phoenix: Convert String column to Integer column

Question

I am looking for a Built-in UDF or any other method to convert values of a string column to integer in my phoenix table for sorting using SELECT and ORDER BY. I searched in the apache language Manual, but no use. Any other suggestions also welcome.

Actual Query

select "values" from "test_table"

I tried below approach but did not work

select TO_NUMBER("values", '\u00A4') from "test_table"

getting error org.apache.phoenix.schema.TypeMismtachException : Error 203 (22005): Type mismatch. VARCHAR and INTEGER for "values" — scoder, Nov 06 '17 at 06:07

Simbosan · Answer 1 · 2021-05-22T08:41:48.190

2

TO_NUMBER returns decimal but you can cast the result to INTEGER

SELECT CAST(TO_NUMBER(MY_COLUMN) AS INTEGER) FROM MY_DB

edited May 22 '21 at 08:41

answered Nov 25 '20 at 01:22

Simbosan

244
2
11

1

I think there is one typo, it should be SELECT CAST(TO_NUMBER(MY_COLUMN) AS INTEGER) FROM MY_DB – syokensyo May 21 '21 at 03:36

score 0 · Answer 2 · answered Jun 18 '20 at 13:03

0

select TO_NUMBER(values) from test_table;

see https://phoenix.apache.org/language/functions.html#to_number

answered Jun 18 '20 at 13:03

IceMimosa

21
3

This converts to DECIMAL, which may not be what the user wants. – Simbosan Nov 25 '20 at 01:21

Phoenix: Convert String column to Integer column

2 Answers2