Help with replace function in hive table

sebastian_gonza
sebastian_gonza New Altair Community Member
edited November 5 in Community Q&A

I have a Hive table in which I have some ID that are 10 characters long, but I have some cases in which is 9 characters, one of them is a point and what I need to do to correct it is to delete the point and add a zero in the beginning of the text, (The origin of the ID is nominal) I tried the following function but it says there is a problem with the array_contains, because is not an array, and I dont see any other function that does that, I saw there are some replace functions available but I dont see them displayed in rapidminer studio 8.2.

 

if(array_contains(cuenta,"."),concat("0",regexp_replace(cuenta,".",""),cuenta))

 

Please help me if you have any idea

Tagged:

Answers

  • mborbely
    mborbely New Altair Community Member

    Hi,

     

    How about using locate?

    select if(locate(".", cuenta) == 0, cuenta,concat("0",regexp_replace(cuenta,"\\.",""))) from table;

     

    Cheers,

    Máté