CODEWITHSUNDEEP
amazon-redshift
Nir Ben Yaacov
Nir Ben Yaacov

Reputation: 1182

How to convert string to integer in amazon redshift

I have a column that holds an id, currently as string. if the id is indeed a number I need to convert it to a real integer and if not, it should be converted to a null value. I would like to run an update query on the table and create a new integer id field.

I was unable to find exactly how to determine if the string is a number

Does any one know?

Thanks

Nir

Upvotes: 0

Views: 8359

Answers (1)

Masashi M
Masashi M

Reputation: 2757

Since Redshift does not support modifying a column type, it's better to create another table with your desired schema. The way is simply inserting a varchar column value into integer and insert it into a new table.

Here is an example:

dev=> CREATE TABLE table_varchar_id (id varchar(24), val varchar(24));
CREATE TABLE
dev=> INSERT INTO table_varchar_id values ('1111', 'aaaa'),('2222', 'bbbb'),('dummy1', 'cccc'),('dummy2', 'dddd');
INSERT 0 4
dev=> CREATE TABLE table_int_id (id int, val varchar(24));
CREATE TABLE
dev=>
dev=> INSERT INTO table_int_id (
dev(> SELECT
dev(>   CASE REGEXP_COUNT(id, '^[0-9]+$')
dev(>   WHEN 0 then NULL
dev(>   ELSE id::integer
dev(>   END as "id",
dev(>   val
dev(> FROM
dev(>   table_varchar_id
dev(> );
INSERT 0 4
dev=> SELECT * FROM table_varchar_id ORDER BY id;
   id   | val
--------+------
 1111   | aaaa
 2222   | bbbb
 dummy1 | cccc
 dummy2 | dddd
(4 rows)

dev=> SELECT * FROM table_int_id ORDER BY id;
  id  | val
------+------
 1111 | aaaa
 2222 | bbbb
      | dddd
      | cccc
(4 rows)

Upvotes: 1

Related Questions