SQL Replace function

• missy786

  SSC Enthusiast

  Points: 132

  More actions

  November 18, 2014 at 3:54 am

  #313262

  I am trying to replace names found in 'xml' fieldname as hyperlinks, by matching with the names in database_tags.

  I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.

  ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))

  RETURNS VARCHAR(MAX)

  AS

  BEGIN

  DECLARE @N VARCHAR(MAX)

  SELECT @N = [Name] FROM [dbo].[database_tags]

  WHERE @XML LIKE '%'+[Name]+'%'

  AND UploadDate >= '2014-09-01'

  IF @N IS NOT NULL

  BEGIN

  SELECT @XML = REPLACE(@XML,

  @N,

  '<a href="<a href="pagename.aspx?tag='+@N+'">'+@N+'</a>')

  END

  RETURN @XML

  END

  for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.

  But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.

  Is there a way to get the function to recognize more than one names as hyperlinks.

  Thank you very much for your time and help.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  November 18, 2014 at 10:27 am

  #1759354

  A variable can only hold one result so all this:

  DECLARE @N VARCHAR(MAX)

  SELECT @N = [Name] FROM [dbo].[database_tags]

  Is returning is the Name from the first row it encounters. So you need to do something like this (not tested at all just off the top of my head)

  SELECT @XML = REPLACE(@XML,

  Name,

  '<a href="<a href="pagename.aspx?tag='+Name+'">'+Name+'</a>')

  FROM

  dbo.database_tags

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question