CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athena
user17012608
user17012608

Reputation: 23

Convert substring to integer

I have written a query and need to extract the second number in a 4 number field. It works well using the substring. However, I need the new field to be an integer, not a string. How do I fix this?

CREATE OR REPLACE VIEW vw_uncnload_new  
AS
    SELECT
        icp,
        SUBSTRING(channel, 2, 1) AS channel_no,
        price_category, 
        network_code,
        msn,
        interval_date,
        interval_number,
        compensation_factor,
        read_value
    FROM
        smartco_prod_meterdata.meter_interval

Upvotes: 0

Views: 4277

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269121

Just use:

CAST(SUBSTRING(channel, 2, 1) AS int) AS channel_no,

Upvotes: 1

Related Questions