英文:
How to extract all words starting with specific character from string in PostgreSQL?
问题
我正在尝试提取所有以'M'字符开头的单词。
例如，我有一个字符串：
"[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]"
我想要得到："Mary, Max"
我尝试使用 regex_replace(\[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}\], '\\W', '', 'g') 来仅保留单词，但不知道提取所有以'M'开头的单词的正确正则表达式。我还尝试了这个：substring("name Mary type Dog name Max type Dog" from '\\w*M\\w*')，但这只给我一个单词。
英文:
I am trying to extract all words that start from 'M' character.
For example I have a string:
"[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]"
I want to get: "Mary, Max"
I tried to regex_replace([{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}], '\W', '', 'g') and left only words, but don't know the correct regex to extract all words starting with 'M'. I also tried this: substring("name Mary type Dog name Max type Dog" from '\w*M\w*') but this gives me only one word.
答案1
得分: 1
你可以使用以下模式。
:\s+"(?=M)(.+?)"
第一组的输出。
Mary
Max
英文:
You can use the following pattern.
:\s+"(?=M)(.+?)"
Output for group 1.
Mary
Max
答案2
得分: 1
你可以使用PostgreSQL的regexp_matches函数。文档在此。
这个查询可能会有帮助。
SELECT array_to_string(regexp_matches(
'[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]',
'"name": "M\\w+"',
'g'
), ', ') AS words;
它返回一个逗号分隔的字符串。'g'标志指定返回所有结果。
另一种获得相同结果的方法是使用LIKE操作符。
SELECT string_agg(value, ', ') AS words
FROM jsonb_array_elements_text(
'[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]'
) AS elem
WHERE value LIKE 'M%';
英文:
You can use Postgresql's regexp_matches function. Documentation here.
This query might help.
SELECT array_to_string(regexp_matches(
'[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]',
'"name": "M\w+"',
'g'
), ', ') AS words;
It returns a comma separated string. The 'g' flag specifies that all results must be returned.
One other way to get the same result is via LIKE operator.
SELECT string_agg(value, ', ') AS words
FROM jsonb_array_elements_text(
'[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]'
) AS elem
WHERE value LIKE 'M%'
答案3
得分: 1
SELECT array_to_string(
REGEXP_MATCHES(
'[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]',
'\mM\w+\M',
'g'
),
', '
) AS result;
英文:
SELECT array_to_string(
REGEXP_MATCHES(
'[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]',
'\mM\w+\M',
'g'
),
', '
) AS result;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论