I have this set of data in postgresql:
en_MY_V2_CID_MY_SEM_1_1_0_0
en_MY_V2_CID_MY_SEM_2_101_1703_0
en_MY_V2_CID_MY_SEM_2_101_1724_0
en_MY_CID_MY_DIS_1_100_17开发者_如何学C05_0068
en_MY_CID_MY_DIS_1_100_1705_0068
I want to extract the substring like these:
en_MY_V2_CID_MY_SEM_1
en_MY_V2_CID_MY_SEM_2
en_MY_V2_CID_MY_SEM_2
en_MY_CID_MY_DIS_1
en_MY_CID_MY_DIS_1
Problem is, the left-side structure is not constant so I can't grasp them using regex. Instead, the right-side structure is constant, with the format _numbers_numbers_numbers_numbers
. So I try to do use this query in postgresql:
select substring(name from '_\d+_\d+_\d+$') from logs;
And get:
1_0_0
101_1703_0
101_1724_0
100_1705_0068
1705_0068
So the question is, how do I negate, or trim the previous substring so I will get my results?
Use rexep_replace
select regexp_replace(name,E'\(\\w\)_\\d+_\\d+_\\d+$',E'\\1') from logs;
精彩评论