CODEWITHSUNDEEP
cassandrascylla
Kokizzu
Kokizzu

Reputation: 26838

How to convert Cassandra/ScyllaDB text to bigint and vice-versa?

In PostgreSQL I can do this:

SELECT (col::BIGINT+1)::TEXT + '%' FROM bar WHERE id = 1
UPDATE bar SET col = (col::BIGINT+1)::TEXT WHERE id = 1

How to do this in Cassandra/ScyllaDB? I need to convert TEXT to BIGINT and back to TEXT to update a column value, the column itself must be a TEXT because it doesn't store only number.

Upvotes: 0

Views: 1577

Answers (1)

Ashraful Islam
Ashraful Islam

Reputation: 12830

There is no default function to this in cql. But you can create one, see UDF. Cassandra support UDF but Scylladb doesn't supports UDF yet

Let's create these function : 

CREATE OR REPLACE FUNCTION bigintAstext (input bigint) CALLED ON NULL INPUT RETURNS text LANGUAGE java AS 'return String.valueOf(input);';

CREATE OR REPLACE FUNCTION textAsbigint (input text) CALLED ON NULL INPUT RETURNS bigint LANGUAGE java AS 'return Long.parseLong(input);';

Here bigintAstext will convert bigint to text and textAsbigint will convert text to bigint

How to use ?

Let's create a table and insert data

CREATE TABLE udf_test (
    id bigint PRIMARY KEY,
    data text
);

INSERT INTO udf_test (id , data ) VALUES ( 10, '10');

Now you can query like : 

SELECT bigintAstext(id), textAsbigint(data) FROM udf_test ;

Output : 

 test.bigintastext(id) | test.textasbigint(data)
-----------------------+-------------------------
                    10 |                      10

Note : UDFs (user defined functions) are disabled by default, you can enable it by setting enable_user_defined_functions: true on cassandra.yaml

Upvotes: 2

Related Questions