hive string extraction

user_13068238 0
Viewed 36

I have a column which has values like https://ysd.com/ert490ghlkh/gsum

I would like to fetch all the characters after com/ and before /gsum. In advance, I know how many characters to fetch after .com/ . Like I know 11 characters have to be fetched after .com.

I tried like

REGEXP_REPLACE(REGEXP_REPLACE(column1, 'https://ysd.com/', ''), '/gsum', '')

It doesn't seem to be ideal because the last part /gsum can vary. In that case, I have to write no of case statements which is equal to unique last part.

Can anyone say how to fetch no of characters after a particular substring?

hiveql hive regex string
2 Answers

You can use

REGEXP_REPLACE(column1, 'https://ysd\\.com/([^/]+).*', '\\1')

See the regex demo

Details

  • https://ysd\\.com/ - a https://ysd.com/ string
  • ([^/]+) - Group 1 (\1 is a backreference to the group value that is used in the replacement pattern): any one or more chars other than /
  • .* - any 0 or more chars other than line break chars, as many as possible.
user_38329700

Extract group between slash:

select REGEXP_REPLACE('https://ysd.com/ert490ghlkh/gsum', '.*/(.*)/.*', '$1')

Result:

ert490ghlkh
user_27003440
Related