Extracting multiple strings between characters

[mutley1]

SQL 2000.....

Hi all,

Trying to help a friend out but this is way over my head. We want to extract a string between 2 specific character sets (it's an varchar field holding an XML string). the thing is, the open / close tag may appear more than once within the main string. E.g. if the open tag is <p> and the close tag is </p>, would like to retrieve the string between the tag. As mentioned, there may be more than one occurrence of open close tag within one field and would like to return both, all 3, all 4 of the strings (seperately) from the one main string.

abc<p>hello John</p>zxzxzxxzx<p>hello Fred</p>erererer""some other rubbish<p>happy new year</p> do be do be do<p>thank you John</p>

desired outcome would be

hello John
hello Fred
happy new year
thank you John

what's the best way to start on this?

TIA peeps,

M.

 

[r937]

what's the best way to start on this?

do it in your application layer





r937.com | rudy.ca

 

[PWise]

Create A numbers table
1 field digit id

CREATE TABLE [Digits] (
	[DigitID] [int] NOT NULL ,
	CONSTRAINT [PK_Digits] PRIMARY KEY  CLUSTERED 
	(
		[DigitID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO
--enter digits from 1 till xxxxxxxxx
declare @s varchar(500)
select @S='abc<p>hello John</p>zxzxzxxzx<p>hello Fred</p>erererer""some other rubbish<p>happy new year</p> do be do be do<p>thank you John</p>'

Select SUBSTRING (@s , Starttag+3 , endtag-Starttag-3 ) 
from(
      Select Starttag , min(endtag) endtag 
      from(
           Select distinct charindex('<p>',@S,digitid)Starttag
           from Digits
           )s
      inner join 
                (Select distinct charindex('</p>',@S,digitid)endtag 
                 from Digits
                 )e
      on e.endtag>s.Starttag
      where Starttag >0
      group by Starttag
      )a
order by Starttag

 

[mutley1]

Thanks Guys,

Sorry for delayed response but you know - the hols and all that, just back in today!

r937 - it's a data extracr that is being done so not really possible through the application.

pwise - thanks, that will work but for 1 field etc. I want to run through a whole table where there may be multiple occurences of <p>sdsdasdd</p> with more that one occurrence of the string in the same field.

Thanks guys,

M.