开发者

How to trim results from a substring-with-regex match?

开发者 https://www.devze.com 2023-02-24 01:50 出处:网络
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

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;
0

精彩评论

暂无评论...
验证码 换一张
取 消