Regex to capture pattern with sql

user_14297619 0
Viewed 45

Given the cell data shown below is of column 'Feed'

hdfs//sddad/aa/vv/cc/SR_DC_EF_GF_20181130_20156478907000_484658274168_CO.dat

i am trying to use a regex method to only display this value 'SR_DC_EF_GF'. Currently i am manually doing a regex method by date which i dont feel its dynamic enough. e,g

select `regexp_replace([Feed], '_2018.*', '')` from tablename.

this will only display and does regex on table that is _20181130. but if i were to have _2019 and _2020, it wont capture and display the whole value. how we can make this regex method dynamic where it can capture other dates?

hive sql
1 Answers

You could look for four digits:

select regexp_replace([Feed], '_[0-9]{4}.*', '')
from tablename;

However, you are only getting rid of the suffix. I think you want something like this to extract the piece you are looking for:

select regexp_replace([Feed], '^.*([^/0-9]+)_[0-9]{4}.*$', '\1')
from tablename;
user_11440350
Related