How to extract middle of the filed data?

[rajneesh]

Hi Team,

In this below table middle of the field we have table name like HRPAL and CDDE.

id

adp_feedparser_EMP_TMF_COURSE_V1_DNH_1_1_HRPAL_20220712T104500

adp_feedparser_EMP_TMF_LRLRNGOFFPRGRS_V1_DNH_1_1_HRPAL_20220712T104500

adp_feedparser_EMP_TMF_COURSE_V1_DNH_1_1_HRPAL_20220714T090000

adp_feedparser_EMP_TMF_LRLRNGOFFPRGRS_V1_DNH_1_1_HRPAL_20220714T090000

adp_feedparser_ORBIT_EMP_CDDE_20220701T200000

adp_feedparser_ORBIT_EMP_CDDE_20220702T194500

adp_feedparser_ORBIT_EMP_CDDE_20220703T193000

adp_feedparser_ORBIT_EMP_CDDE_20220704T194500

I want create new field with only two words instead of entire column name.

Expected output:

ID

HRPAL

HRPAL

CDDE

CDDE

like that. Please help me on this.

Thanks

Raj

[Taoufiq_Zarra · Accepted solution]

@rajneesh  many solutions, for example :

Data:

LOAD id,if(wildmatch(id,'*HRPAL*','*CDDE*')=1,'HRPAL','CODE') as newid INLINE [
    id
    adp_feedparser_EMP_TMF_COURSE_V1_DNH_1_1_HRPAL_20220712T104500
    adp_feedparser_EMP_TMF_LRLRNGOFFPRGRS_V1_DNH_1_1_HRPAL_20220712T104500
    adp_feedparser_EMP_TMF_COURSE_V1_DNH_1_1_HRPAL_20220714T090000
    adp_feedparser_EMP_TMF_LRLRNGOFFPRGRS_V1_DNH_1_1_HRPAL_20220714T090000
    adp_feedparser_ORBIT_EMP_CDDE_20220701T200000
    adp_feedparser_ORBIT_EMP_CDDE_20220702T194500
    adp_feedparser_ORBIT_EMP_CDDE_20220703T193000
    adp_feedparser_ORBIT_EMP_CDDE_20220704T194500
];

outpout: