SQL Replace Function - error recognizing two or more strings

miss786

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,
'['+@N+'](<a href=)')
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.

Richard Deeming

Try this:

ALTER FUNCTION [dbo].[ReplaceTags]( @XML VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN
SELECT
@XML = Replace(@XML, [Name], '<a href="pagename.aspx?tag=' + [Name] + '>' + [Name] + '</a>')
FROM
[dbo].[database_tags]
WHERE
@XML LIKE '%' + [Name] + '%'
And
UploadDate >= '2014-09-01'
;

RETURN @XML;

END
GO

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

PhilLenoir

Your problem is caused by the fact that only one name match will work. Although your first select statement will potentially return multiple rows @N will only ever have one value. Using Richard's method you are wrapping everything in together. Your approach would have required being wrapped in a cursor to enumerate your first select.

Life is like a s**t sandwich; the more bread you have, the less s**t you eat.

miss786

Thank you very much for your reply and help. I have already tried the above approach and unfortunately, it gives me incorrect output. I pass the following XML Input to the UDF:

<Body>

One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.

</Body>

the function above, outputs the following (which is incorrect).

One is a £1m block of [AIREM 2006-1X 2A3](<a href=)">[AIREM 2006-1X 2A3](<a href=)">[AIREM 2006-1X 2A3](<a href=)">[AIREM 2006-1X 2A3](<a href=)

The desired output should be :

<Body>

One is a 1m block of [AIREM 2006-1X 2A3](pagename.aspx?tag=AIREM 2006-1X 2A3), which has never appeared on SMO.

</Body>

I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are. http://sqlfiddle.com/#!6/96cac8/2 I looked into a cursor approach for this replace function and have come up with the following below. However, I am still experiencing the same output error, as explained above. the function loops through continuously and creates duplicate names of hyperlinks, within the XML data.

ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT name
FROM [dbo].[database_tags]
Where UploadDate >= '2014-09-01'
and @XML LIKE '%' + Name + '%'

OPEN CUR

WHILE 1 = 1
BEGIN
FETCH cur INTO @name
--IF @Name IS NOT NULL
IF @@fetch_status <> 0
BREAK
BEGIN
SELECT @XML = REPLACE(@XML,
@Name,
'['+@Name+'](<a href=)')
END
--FETCH NEXT FROM CUR INTO @Name
END

CLOSE CUR;
DEALLOCATE CUR;

RETURN @XML
END

Please advice further, if possible. Thank you for your help and time.

Richard Deeming

I can't reproduce the problem using your SQL Fiddle data and the code I posted.

Article 1:[^]

<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3>AIREM 2006-1X 2A3</a>, which has never appeared on SMO.<p></Body>

Article 2:[^]

<body>It consists of: <a href="pagename.aspx?tag=BANKP>BANKP</a>, <a href="pagename.aspx?tag=BCJA>BCJA</a>, <a href="pagename.aspx?tag=BCJA>BCJA</a>M, <a href="pagename.aspx?tag=<a href="pagename.aspx?tag=BFTH>BFTH</a>><a href="pagename.aspx?tag=BFTH>BFTH</a></a>, <a href="pagename.aspx?tag=<a href="pagename.aspx?tag=BFTH>BFTH</a>><a href="pagename.aspx?tag=BFTH>BFTH</a></a>, and <a href="pagename.aspx?tag=EMPOP>EMPOP</a>.</body>

DECLARE @XML nvarchar(max);

SELECT
@XML = XML
FROM
article
WHERE
id = '1'
;

SELECT
@XML = Replace(@XML, [Name], N'<a href="pagename.aspx?tag=' + [Name] + N'>' + [Name] + N'</a>')
FROM
[dbo].[database_tags]
WHERE
@XML LIKE N'%' + [Name] + N'%'
;

SELECT @XML As Result;

NB: You should avoid using text, ntext or image types in SQL. They are deprecated and will be removed in a future version[^]. Use varchar(max), nvarchar(max) and varbinary(max) instead.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

PhilLenoir

Please note that I've repeated Richard's solution and don't get any problem either. I know that it's frustrating! Can we see the code you are using to call the UDF?

Life is like a s**t sandwich; the more bread you have, the less s**t you eat.

miss786

Thank you for your suggestion and help. I am manage to get the cursor function work by adding a distinct clause in the tags select query of function:

DECLARE @fullame VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT distinct name
FROM database_tags
Where UploadDate >= '2014-09-01'

Thank you very much Richard for your solution, I am sorry to inform, the above solution was little slow loading the articles with hyperlinks name, hence I choose the cursor function approach. Thank you all, for your time and suggestion for this post. I appreciate all your help.

PhilLenoir

You are welcome! Richard's solution should have worked - that is the one I also tested and is exactly the approach I would have used. I only mentioned the cursor to help you understand the problem, but if it works ...! If Richard's query is performing badly, it could probably be improved with a well-chosen additional index. When you have some time, I'd suggest running it with the SQL query analyzer. Good luck!

Life is like a s**t sandwich; the more bread you have, the less s**t you eat.