Hi, Thank you, I figured this out using substring substr(item_desc,instr(item_desc,' ')+1,length(item_desc)) This is giving me the desired results Regards Souveek ... View more

@Vinicius Higa Murakami ... View more

Thanks a lot, this worked. but for my requirement I used the below syntax from_unixtime(unix_timestamp(date_add(add_months(current_timestamp,-1),-9) ,'yyyy-MM-dd'), 'yyyyMM') Thanks a lot. Regards, Souveek ... View more

Hi, @Vinicius Higa Murakami Thank you for taking out your time to help me. The regexp_extract didnt work correctly but I got the idea and modified the code to get the desired results. Here is what I did: trim(concat_ws(' ', case when instr( lower(col4) , 'abcd' ) >0 then 'abcd' else '' end, case when instr( lower(col4) , 'pqrs' ) >0 then 'pqrs' else '' end)) This worked perfectly. Thank you so much Regards Souveek ... View more

Let me give you an example of the code that is working in postgres based on the code I gave. CREATE OR REPLACE VIEW XYZ_V AS( Select col1, col2, col3, col4,trim(both ' ' from case when substring( lower(col4) from 'abcd' ) is null then '' else substring( lower(col4) from 'abcd') end ||' '|| case when substring( lower(col4) from 'pqrs' ) is null then '' else substring( lower(col4) from 'pqrs') end ||' '|| case when substring( lower(col4) from 'mnop' ) is null then '' else substring( lower(col4) from 'mnop') end) as col5 FROM XYZ); Example: COL1\t COL2\t COL3\t COL4\t COL5 1\t pdf\t page1\t efgh opsdw mnop\t mnop 2\t txt\t page4\t abcd qwerty pqrs\t abcd pqrs 3\t txt\t page3\t abcd pqrs xyzsd mnop\t abcd pqrs mnop (\t denotes the break in columns) Regards, Souveek ... View more

Thank you for replying but my main concern is to replicate the part using substring in Hive. I can't figure out a way to get the exact output ... View more