Dynamic Parsing of String 3

[redtoad]

This query -

DECLARE @Note varchar(255)

SELECT @Note = NoteText
FROM Notes
WHERE RecordID=5173

Returns the following string:

Please review and provide feedback.<br><br><a href=test>Candidate cjtest54r jtest54r</a><br><br><a href=test>Candidate cjtest08r jtest08r</a><br><br>

I use the following commands to parse out the text:

IF (charindex ('<a', @Note, 0)) > 0
BEGIN
DECLARE @URLstart int
SET @URLstart = charindex('<a', @Note, 0)
DECLARE @URLend int
SET @URLend = charindex('>', @Note, @URLstart)
DECLARE @Part1 varchar(255)
SELECT @Part1 = substring(@Note, 1, (@URLstart - 1) )
DECLARE @Part2 varchar(255)
SELECT @Part2 = substring(@Note, (@URLend + 1), len(@Note))
SET @Part2 = substring (@Part2, 1, (charindex('</a>', @Part2, 0)) - 1)

SET @Note = @Part1 + @Part2
IF (charindex ('<a', @Note, @URLend))>0
BEGIN
SELECT 'test'
END

END
SELECT @Note

The result is this:

Please review and provide feedback.<br><br>Candidate cjtest54r jtest54r

However, I need the results to return this:

Please review and provide feedback.<br><br>Candidate cjtest54r jtest54r<br><br>Candidate cjtest08r jtest08r

The query only returns the first Candidate, I need the results to return all of the Candidates in the string, and the number of candidates is not static. In other words there can be from 1 to an infinite (theoretically)number of candidates in any given string.

Any help would be appreciated.

 

[clarkin]

Have a look at WHILE and PATINDEX:

WHILE PATINDEX('%Candidate%', @Note) > 0
BEGIN
   --get candidate stuff out of @note
   --then trim note and repeat
END

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[gavinhuet]

Try this:

DECLARE @Note     VARCHAR(500),
        @URLstart INT,
        @URLend   INT
SET @Note = 'Please review and provide feedback.<br><br><a href=test>Candidate cjtest54r jtest54r</a><br><br><a href=test>Candidate cjtest08r jtest08r</a><br><br>'
SET @Note = REPLACE(@Note, '</a>', '')
WHILE (charindex ('<a href=', @Note, 0) > 0) BEGIN
  SET @URLstart = charindex('<a href=', @Note, 0)
  SET @URLend = charindex('>', @Note, @URLstart)
  SET @Note=STUFF(@Note, @URLstart, @URLend-@URLstart+1, '')
END
SELECT @Note

Gavin

 

[LogicalmanUS]

redtoad,

here you go, copy and paste this as a SProc and then adjust as you need to:

-- Created by Logicalman for Tek Tips 091603

CREATE PROCEDURE [dbo].[sp_Parse_URL]

AS
DECLARE @Note varchar(255)
DECLARE @URLstart int
DECLARE @URLend int
DECLARE @Part1 varchar(255)
DECLARE @Part2 varchar(255)
DECLARE @sOUTPUT varchar(1000)

SET @Note = 'Please review and provide feedback.<br><br><a href=test>Candidate cjtest54r jtest54r</a><br><br><a href=test>Candidate cjtest08r jtest08r</a><br><br>'

SET @URLstart = charindex('<br>', @Note, 0)
PRINT @URLstart
SET @Part1 = substring(@Note, 1, (@URLstart - 1) )
PRINT @Part1
SET @sOUTPUT = ''
SET @URLstart = charindex('<a', @Note, 0)
WHILE (@URLstart>0)
BEGIN
SET @URLend = charindex('>', @Note, @URLstart)
SET @Part2 = substring(@Note, (@URLend + 1), len(@Note))
SET @Part2 = substring (@Part2, 1, (charindex('</a>', @Part2, 0)) - 1)
SET @sOUTPUT = @sOUTPUT + '<BR><BR>' + @Part2
SET @Note = substring(@Note, charindex('</a>', @Note)+4, len(@Note))
SET @URLstart = charindex('<a', @Note, 0)
END
SET @sOUTPUT = @Part1 + @sOUTPUT
SELECT @sOUTPUT

GO


Logicalman

 

[redtoad]

Thanks for everyone's input, each response was helpful. I ended up using something very similar to gavinhuet's response.

Thanks again.