如何在 PostgreSQL 中的字符串中查找特定字符的第一次和最后一次出现

How to find the first and last occurrences of a specific character inside a string in PostgreSQL

提问by MD Sayem Ahmed

I want to find the first and the last occurrences of a specific character inside a string. As an example, consider a string named "2010-####-3434", and suppose the character to be searched for is "#". The first occurrence of hash inside the string is at 6-th position, and the last occurrence is at 9-th position.

我想在字符串中找到特定字符的第一次和最后一次出现。例如，考虑一个名为“2010-####-3434”的字符串，假设要搜索的字符是“#”。字符串中第一次出现哈希在第 6 位，最后一次出现在第 9 位。

采纳答案by Peter Tillemans

I do not know how to do that, but the regular expression functions like regexp_matches, regexp_replace, and regexp_split_to_arraymay be an alternative route to solving your problem.

我不知道该怎么做，但是正则表达式的功能类似于regexp_matches, regexp_replace, 并且regexp_split_to_array可能是解决问题的替代途径。

回答by rfusca

Well...

好...

Select position('#' in '2010-####-3434');

will give you the first. If you want the last, just run that again with the reverse of your string. A pl/pgsql string reverse can be found here.

会给你第一个。如果你想要最后一个，只需用你的字符串的反向再次运行。可以在此处找到 pl/pgsql 字符串反向。

Select length('2010-####-3434') - position('#' in reverse_string('2010-####-3434')) + 1;

回答by Autumn Gao

In the case where char = '.', an escape is needed. So the function can be written:

在 的情况下char = '.'，需要转义。所以函数可以写成：

CREATE OR REPLACE FUNCTION last_post(text,char) 
RETURNS integer LANGUAGE SQL AS $$  
select length()- length(regexp_replace(, E'.*\' || ,''));  
$$;

回答by Evan Carroll

9.5+ with array_positions

9.5+ 与 array_positions

Using basic PostgreSQL array functionswe call string_to_array(), and then feed that to array_positions()like this array_positions(string_to_array(str,null), c)

使用我们调用的基本PostgreSQL 数组函数string_to_array()，然后将其提供给array_positions()这样的array_positions(string_to_array(str,null), c)

SELECT
  arrpos[array_lower(arrpos,1)] AS first,
  arrpos[array_upper(arrpos,1)] AS last
FROM ( VALUES
  ('2010-####-3434', '#')
) AS t(str,c)
CROSS JOIN LATERAL array_positions(string_to_array(str,null), c)
  AS arrpos;

回答by leonbloy

This pure SQL function will provide the last position of a char inside the string, counting from 1. It returns 0 if not found ... But (big disclaimer) it breaks if the character is some regex metacharacter ( .$^()[]*+)

这个纯 SQL 函数将提供字符串中 char 的最后一个位置，从 1 开始计数。如果没有找到，它返回 0 ......但是（大免责声明）如果字符是某个正则表达式元字符 ( .$^()[]*+)

CREATE FUNCTION last_post(text,char) RETURNS integer AS $$ 
     select length()- length(regexp_replace(, '.*' || ,''));
$$ LANGUAGE SQL IMMUTABLE;

test=# select last_post('hi#-#-#byte','#');
 last_post
-----------
         7

test=# select last_post('hi#-#-#byte','a');
 last_post
-----------
         0

A more robust solution would involve pl/pgSQL, as rfusca's answer.

作为 rfusca 的答案，更强大的解决方案将涉及 pl/pgSQL。

回答by lub0v

Another way to count last position is to slit string to array by delimeter equals to needed character and then substract length of characters for the last element from the length of whole string

计算最后一个位置的另一种方法是通过等于所需字符的分隔符将字符串切割成数组，然后从整个字符串的长度中减去最后一个元素的字符长度

CREATE FUNCTION last_pos(char, text) RETURNS INTEGER AS
$$
select length() - length(a.arr[array_length(a.arr,1)]) 
from (select string_to_array(, ) as arr) as a
$$ LANGUAGE SQL;

For the first position it is easier to use

对于第一个位置，它更容易使用

select position('#' in '2010-####-3434');

回答by Tomasz Kozik

My example:

我的例子：

reverse(substr(reverse(newvalue),0,strpos(reverse(newvalue),',')))

1. Reverse all string
2. Substring string
3. Reverse result

1. 反转所有字符串
2. 子串串
3. 反转结果