Postgres:将文本提取到字符串中的第 N 个字符 [英] Postgres: extract text up to the Nth Character in a String
问题描述
如何将文本提取到列中字符的第 4 个实例?
How can I extract the text up to the 4th instance of a character in a column?
我从名为 filter_type 的列中选择文本，直到第四个 > 字符.
I'm selecting text out of a column called filter_type up to the fourth > character.
为了做到这一点，我一直试图找到第四个 > 字符的位置，但它不起作用:
To accomplish this, I've been trying to find the position of the fourth > character, but it's not working:
select substring(filter_type from 1 for position('>' in filter_type))
推荐答案
您可以使用 模式匹配 功能.
首先找出一个模式来捕获直到第四个 > 字符的所有内容.
First figure out a pattern to capture everything up to the fourth > character.
要开始您的模式，您应该创建一个捕获非 > 字符和一个 > 字符的子组:
To start your pattern you should create a sub-group that captures non > characters, and one > character:
([^>]*>)
然后捕获四次以到达 >
([^>]*>){4}
然后，您需要将其包装在一个组中，以便匹配带回所有四个实例:
Then, you will need to wrap that in a group so that the match brings back all four instances:
(([^>]*>){4})
并放置一个字符串符号的开头以确保它仅从字符串的开头(而不是中间)匹配:
and put a start of string symbol for good measure to make sure it only matches from the beginning of the String (not in the middle):
^(([^>]*>){4})
一旦您拥有将在第一个组元素中返回您想要的内容的模式(您可以在右侧面板的在线正则表达式中得知)，您需要在 SQL 中重新选择它.
Once you have the pattern that will return what you want in the first group element (which you can tell at the online regex on the right side panel), you need to select it back in the SQL.
在 Postgres 中，子字符串函数有一个选项使用正则表达式模式在子字符串中使用from"语句从输入中提取文本.
In Postgres, the substring function has an option to use a regex pattern to extract text out of the input using a 'from' statement in the substring.
最后，把它们放在一起！
select substring(filter_type from '^(([^>]*>){4})')
from filter_table
如果您想在 > 的实例少于四个时匹配整个字符串，请使用以下正则表达式:
If you want to match the entire string whenever there are less than four instances of >, use this regular expression:
^(([^>]*>){4}|.*)
这篇关于Postgres:将文本提取到字符串中的第 N 个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
