Find substrings within between 2 string fragments

Question

I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code.

CREATE OR REPLACE VIEW vw_actions AS 
 SELECT ls.f_table_schema, ls.f_table_name, 
 (SELECT substr(ls.attribute_actions_text, 
 strpos(ls.attribute_actions_text, 'name="')+6, 
 strpos(ls.attribute_actions_text, '"/>') - 
 strpos(ls.attribute_actions_text, 'name="'))) AS actions
 FROM layer_styles ls;

The outcome is that it doesn't like minus numbers when using strpos. I can get it go forward 6 characters to remove the 'name="' from the returned substring but cannot remove the '"/>'.

It returns the following:

View SHED Database"/> 

where I want it return:

View SHED Database

Any suggestions would be greatly appreciated.

ADDITION:I have found out that if I was using 9.1 I could have used strposrev and i think the following code would have worked:

CREATE OR REPLACE VIEW vw_actions AS 
 SELECT ls.f_table_schema, ls.f_table_name, 
 (SELECT substr(ls.attribute_actions_text::text, 
 strpos(ls.attribute_actions_text::text, 'name="'::text)+6, 
 strposrev(ls.attribute_actions_text::text, '"/>'::text)+3 - 
 strpos(ls.attribute_actions_text::text, 'name="'::text))) AS actions
 FROM layer_styles ls;

• oNare: Try replace(ls.f_table_schema,'"/>','')?
• Erwin Brandstetter: I would have expected the definition of the underlying table (\d tbl in psql) and some sample values for this question.
• daniel franklin: I will do so in further questions, thanks for the advice.

Answer 1

Use substring() with a regular expression instead:

substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')

The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned.

Like your code, this selects the first string matching the pattern and does not look further.

Also, you don't need to make your expression a subquery, that adds nothing but overhead:

CREATE OR REPLACE VIEW vw_actions AS 
SELECT ls.f_table_schema
 , ls.f_table_name
 , substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') AS actions
FROM layer_styles ls;

Quick test case (you should have provided):

SELECT *, substring(ls.attribute_actions_text FROM 'name="(.*?)"/>')
FROM (
 VALUES
 ('bar name="View SHED Database"/> foo')
 , ('bar name="View SHED Database"/> fooname="View SHED Database"/>xx')
 , ('name="buntch a bull"/> fooname="View SHED Database"/>xx')
 , ('xxname="bla foo grr"/>')
 , ('')
 , (NULL)
 ) ls(attribute_actions_text)

• daniel franklin: Erwin, thanks for this. I knew I should of added a test case showing my inputs and will do so in future. What does the (.*?) do? It works perfectly though, thankyou very much.
• Erwin Brandstetter: @danielfranklin: I added explanation & link above.
• Andrew T: This still works great in 2025 with regex101.com to debug and explain samples.