Extract substring with a specific pattern in Hive SQL

Question

I have a column with this sample data. I need to extract all substring that starts with "M6". Is there a way to do it with regexp_extract?

Gurmanjot Singh · Accepted Answer · 2021-04-04 04:27:10Z

2

Try using:

SELECT colname FROM tableName WHERE REGEXP_EXTRACT(colname, ".*(M6[^_]*).*",1)

Regex used:

.*(M6[^_]*).*

Explanation:

.* - matches 0+ occurrences of any character that is not a newline character
(M6[^_]*) - matches M6 followed by 0+ occurrences of any character that is not a _. So, after M6, it keeps on matching everything until it finds the next _. The parenthesis is used to store this sub-match in Group 1
.* - matches 0+ occurrences of any character that is not a newline character

answered Apr 4, 2021 at 4:27

Gurmanjot Singh

10.4k2 gold badges22 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1