Extract all characters before numeric values in hive SQL

Question

I have a column COD with data that looks like this:

COD
TPP
PHONE_9985
SMS@TELL-1256
TEXT@123

I want to extract all characters before numeric values, expect result is:

COD
TPP
PHONE
SMS@TELL
TEXT

I tried this, but without success:

substr(cod, 1, instr(cod, regexp_replace(cod, '[^0-9]','')))

Answer 1

use built in regexp replace

SELECT
    COD,
    CASE
        WHEN LOCATE('0', COD) = 0
            OR LOCATE('1', COD) = 0
            OR LOCATE('2', COD) = 0
            OR LOCATE('3', COD) = 0
            OR LOCATE('4', COD) = 0
            OR LOCATE('5', COD) = 0
            OR LOCATE('6', COD) = 0
            OR LOCATE('7', COD) = 0
            OR LOCATE('8', COD) = 0
            OR LOCATE('9', COD) = 0 THEN COD
        ELSE SUBSTR(COD, 1, LEAST(
            COALESCE(NULLIF(LOCATE('0', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('1', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('2', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('3', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('4', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('5', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('6', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('7', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('8', COD), 0), 9999999),
            COALESCE(NULLIF(LOCATE('9', COD), 0), 9999999)
        ) - 1)
    END AS characters_before_numeric
FROM
    your_table_name;