Replace sql function

Discussion:

(too old to reply)

Missy

2014-11-17 20:30:00 UTC

I am little bit struggling to get the my sql function below, to execute the correct output:

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

SELECT @XML = REPLACE(@XML,[Name],'<a href="<a href="pagename.aspx?tag='+[name]+'">'+[name]+'</a>')
FROM [dbo].[database_tags]
where UploadDate >= '2014-09-01'

RETURN @XML
END

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

for example, the following input is passed into the UDF:

<Body>One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.</Body>
and the function outputs the following (which is incorrect).

One is a &#163;1m block of <a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a></a>"><a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>

I believe the issue, is in function's loop (select statement).

Is there a way, I can improve the UDF, so, as soon as it finds a matching name from database_tags table, it exit the loop.

Is there an example I could look into, which can help me further solve this issue.

Many thanks

rpresser

2014-11-17 20:47:20 UTC

Permalink

Post by Missy
Is there an example I could look into, which can help me further solve this issue.

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

DECLARE @N VARCHAR(MAX)
SELECT TOP 1 @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

Missy

2014-11-17 21:32:00 UTC

Permalink

Thank you very much for your reply and help.
I tested the above function, it performs really good, if the XML fieldname has one name but it fails to recognize, if XML content has more than one names, that match the 'database tags' names.

for example, the input data:
It consists of: BANKP I A2, BCJAF 6 A2, BCJAM 1 A, BFTH 10 A2, BFTH 11 A2, BFTH 3 A, BFTH 8 A, BFTH 9 A2T, BFTH 9 A2T, and EMPOP 2006-1 A2

the function is only able to recognize the following two names:
BFTH 9 A2T, BFTH 9 A2T and outputs them as hyperlinks.

whereas the other names are not recognized.
I chnaged the functions select and where clause as below, but then the function, fails to recognize no names as hyperlinks:
-----------------------------
SELECT @N = [Name] FROM [dbo].[database_tags]

WHERE @XML = '+[Name]+'
----------------------------

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.

Erland Sommarskog

2014-11-17 22:12:12 UTC

Permalink

Post by Missy
Thank you very much for your reply and help.
I tested the above function, it performs really good, if the XML
fieldname has one name but it fails to recognize, if XML content has
more than one names, that match the 'database tags' names.

You would need to run a cursor:

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Name
FROM database_tags
WNERE @XML LIKE '%' + Name + '%'

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @name
IF @@fetch_status <> 0
BREAK

SELECT @XML = replace(@XML, @name, '<a href= ....>')
END

DEALLOCATE cur

--
Erland Sommarskog, Stockholm, ***@sommarskog.se

Missy

2014-11-18 12:18:59 UTC

Permalink

Thank you for your reply and suggestion. Apology for the late response. I tried converting your approach into my replaceTags function, but I am sorry to inform, that the output from the function, is incorrect.

---------------------------------
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,
'<a href="<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>')
END
--FETCH NEXT FROM CUR INTO @Name
END

CLOSE CUR;
DEALLOCATE CUR;

RETURN @XML
END

-------------------------------------
(*) 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 outputs the following (which is incorrect).

One is a &#163;1m block of <a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a></a>"><a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>

The desired output should be :
<Body>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.</Body>

Here is the sample data from my database_tags table:
1,BANKP,
2,BCJA,
3,BCJAM,
4,BFTH,
5,BFTH,
6,EMPOP,
7,AMPT
8,AIREM 2006-1X 2A3

Is there something I am doing wrong in my code above?
Please advice further, if possible. Thank you for your time and help.

Erland Sommarskog

2014-11-18 22:14:44 UTC

Permalink

For the continuing story, please see
http://social.Msdn.microsoft.com/Forums/en-US/transactsql/thread/42c1a941-8c4b-454d-8153-1def98db8af1#7549cb9f-cc3b-483b-b11c-f8df1ba88c47

Let's keep the thread in one place.