SQL: Cast VARCHAR to BIGINT after TRIM leading characters

user_13142245 0
Viewed 116

I have VARCHAR IDs but need BIGINT ids.

-- have: ORG1234
--need: 1234

SELECT 
    CAST(TRIM('ORG' FROM ads_solutions_advertiser_id) AS BIGINT) AS num_id
FROM 
    tab

>>>
Syntax error: mismatched input 'CAST'. Expecting: <query>

Followed this tutorial: SELECT TRIM('#! ' FROM ' #SQL Tutorial! ') AS TrimmedString;

What's this simplest way to fix this?

presto sql
1 Answers

trim in presto works in different way:

Removes leading and trailing whitespace from string.

You can use replace:

replace(string, search) → varchar
Removes all instances of search from string.

SELECT CAST(replace('ORG1234', 'ORG') AS BIGINT) AS num_id

Output:

num_id
1234
user_25012790
Related