Search code examples
stringhivesubstringimpala

Remove a substring from the string in impala

I have two columns like below:

a_user_id b_user_id
45610123     456
567232436    567

means a_user_id is some_number + b_user_id I want to create a column c_userid where I have removed the b_user_id from a_user_id. I am using Impala hive. sql command to do this don't work in this.

Solution

  • You can try regexp_extract:

    select
    *,
    regexp_extract(a_user_id, concat(b_user_id, '(.*)'), 1) as c_user_id
from mytable;

    Or regexp_replace:

    select 
    *, 
    regexp_replace(a_user_id, concat('^', b_user_id), '') as c_user_id 
from mytable;