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;

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)