Convert a varchar column to integer in Redshift

0
votes

Is there a way in Amazon Redshift to convert a varchar column (with values such as A,B,D,M) to integer (1 for A, 2 for B, 3 for C...and so on) ? I know teradata has something like ASCII() but that doesn't work in Redshift.

Note: My goal is to convert the varchar columns to a number in my query and compare those two columns to see if the numbers are same or different.

1
postgresqlamazon-redshift

1 Answers

1
votes

demo:db<>fiddle

Postgres
SELECT 
    ascii(upper(t.letter)) - 64
FROM
    table t

Explanation:

  • upper() makes the input to capital letters (to handle the different ascii value for capital and non-capital letters)
  • ascii() converts the letters to ASCII code. The capital letters begin at number 65.
  • decrease the input by 64 to shift from ASCII starting point == 65 downto 1

Redshift:

The ascii() function is marked as deprecated on Redshift (https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions_leader_node_only.html)

So one possible (and more pragmatic) solution is to get a fixed alphabet string and give out the index for a given letter:

SELECT 
    letter, 
    strpos('ABCDEFGHIJKLMNOPQRSTUVWXYZ', upper(t.letter))
FROM 
    table t