如何获得字符串中所有匹配的位置? [英] How get all matching positions in a string?
问题描述
我在PostgreSQL的表中有一个列 flag_acumu
,其值如下:
<$我需要显示所有带有'S'的职位。有了这段代码,我只会得到第一个这样的职位,而不会得到后面的职位。SELECT codn_conce,flag_acumu,position(' S'IN flag_acumu)AS the_pos
从dh12
WHERE位置('S'IN flag_acumu)!= 0
按the_pos ASC排序;
如何获得所有这些?
解决方案在Postgres 9.4 或更高版本中,您可以方便地将
unnest()
与<$ c $结合使用c>有序性:选择*
FROM dh12 d
JOIN unnest(string_to_array(d.flag_acumu,NULL))
具有序数u(elem,the_pos)ON u.elem ='S'
在哪里d.flag_acumu喜欢'%S%'-可选,请参见低于
,按d.codn_conce,u.the_pos排序;
每次比赛返回一行。
WHERE d.flag_acumu LIKE‘%S%’
是可选的,可快速消除没有任何匹配项的源行。
较旧版本的详细说明和替代方法:
I have a column flag_acumu
in a table in PostgreSQL with values like:
'SSNSSNNNNNNNNNNNNNNNNNNNNNNNNNNNNSNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN'
I need to show all positions with an 'S'. With this code, I only get the first such position, but not the later ones.
SELECT codn_conce, flag_acumu, position('S' IN flag_acumu) AS the_pos
FROM dh12
WHERE position('S' IN flag_acumu) != 0
ORDER BY the_pos ASC;
How to get all of them?
In Postgres 9.4 or later you can conveniently use unnest()
in combination with WITH ORDINALITY
:
SELECT *
FROM dh12 d
JOIN unnest(string_to_array(d.flag_acumu, NULL))
WITH ORDINALITY u(elem, the_pos) ON u.elem = 'S'
WHERE d.flag_acumu LIKE '%S%' -- optional, see below
ORDER BY d.codn_conce, u.the_pos;
This returns one row per match.
WHERE d.flag_acumu LIKE '%S%'
is optional to quickly eliminate source rows without any matches. Pays if there are more than a few such rows.
Detailed explanation and alternatives for older versions:
这篇关于如何获得字符串中所有匹配的位置?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!