Postgresql how match content like "[\s\S]*?" in regex

Question

I want to replace content of long Strings in postgres column.

The match pattern is "{Begin}{anything}{mid}{anything}{end}", and the match is non-greedy version.

Begin, mid, end is fixed content while anything can include character, number, space, newline etc.

With common regex we can use ([\s\S]*?) but it doesn't work well in Postgres. How can I do with this.

Example: I have a DB column name "descriptor", I want to match content

 "<function type="class"> {anything} <arg name="class.name">com.ehi.jira.plugin.workflow {anything} </function>",

and it should match the shortest content.With common regex I will use

"<function type="class">([\s\S]*?)<arg name="class.name">com.ehi.jira.plugin.workflow([\s\S]*?)</function>".

But it doesn't work in postgresql.

You need to use `.` instead of `[\s\S]`, it is all discussed [here](https://stackoverflow.com/a/45981809/3832970) — Wiktor Stribiżew, Nov 28 '18 at 07:40

score 0 · Accepted Answer · answered Nov 28 '18 at 07:35

0

You can use the % operator to match any number of characters:

select * from table where col like '<function type="class">%<arg name="class.name">com.ehi.jira.plugin.workflow%</function>'

A sample query to test this :

postgres=# select * from (select '<function type="class"> {anything} <arg name="class.name">com.ehi.jira.plugin.workflow {anything} </function>' as col) as t  where col like '<function type="class">%<arg name="class.name">com.ehi.jira.plugin.workflow%</function>' ;

                                                      col
---------------------------------------------------------------------------------------------------------------
 <function type="class"> {anything} <arg name="class.name">com.ehi.jira.plugin.workflow {anything} </function>
(1 row)

answered Nov 28 '18 at 07:35

FacePalm

7,518
3
37
44

Thanks for your answer. I find my question is wrong. I want it contain "" only once. But it seems regex can't do it. In detail, the matched content should be "chars numbers newlines spaces etccom.ehi.jira.plugin.workflow chars numbers newlines spaces etc", and it contains "" and only once. Can regex do that? – Slinn Shi Nov 28 '18 at 07:39
You can force to be the beginning and end with `^` and `$` characters – FacePalm Nov 28 '18 at 07:46
The content is generated by other application, I can't set the rule. I realize it's a strange requirement. I think it can't be done. Thanks for your kindly answer. – Slinn Shi Nov 28 '18 at 07:51

Postgresql how match content like "[\s\S]*?" in regex

1 Answers1