TSQL - Wrap HTTP:// or HTTPS:// with HTML Blue and Bold Font Tags from a varchar(max) column Part 3

Question

This is working most of the time....

https://learn.microsoft.com/en-us/answers/questions/185432/tsql-wrap-http-or-https-with-html-blue-and-bold-fo-1.html

But one of our notes column contains over 27,000 characters, and many, in this case 30 different http hyperlinks. This is an unusual case, however, we need to consider this.

Is there a way we can account for this large number of characters ? and/or the large number of hyperlinks in this note ?

Thank you !

Answer 1

It shouldn't be a problem for XML/XQuery approach.
You just need to make sure that the column data type is VARCHAR(MAX) or NVARCHAR(MAX).

UPDATE

Upon analysis, the resolution for the null value is very simple. The Resolution Note column contains lots of ampersands. It is a reserved character in XML. Unfortunately, this forum doesn't allow to use a CDATA section in the source code. That's why I had to remove it.

Please see it in the picture. You would need to add CDATA to the T-SQL statement, in 4 spots
After that everything will work.

P.S. I complained about it to Microsoft many times to no avail.

UPDATE #2
I optimized the XQuery FLWOR expression. The duration now is around 100 milliseconds instead of minutes!

XQuery fragment

for $x in /root/r/text()  
return if (substring($x, 1, 4) eq "http") then  
concat("<br><br><font color=""blue""><b>", $x, "</b></font><br><br>")  
else data($x)  

Answer 2

I hear what you are saying, this is the code, something isn't right - it returns a null value for the RNote, when there are 27,000 characters in the Record_Notes column:

SELECT 
 Trim(SV000805.Service_Call_ID) as Service_Call_ID,
 Trim(CUSTNMBR) as CUSTNMBR,
 Trim( ADRSCODE) as ADRSCODE,
 Trim(Reference_ID) as Reference_ID, 
 SV000805.Record_Notes,
  TRY_CAST('<root><r>' + 
                  REPLACE(Replace(Trim(convert(varchar(max),SV000805.Record_Notes)),'http',' http'), SPACE(1), '</r><r>') + 
         '</r></root>' AS XML).query('
  for $x in /root/r
  return if (substring(($x/text())[1],1,4) eq "http") then
  concat("<br><br><font color=""blue""><b>", ($x/text())[1], "</b></font><br><br>")
  else data($x)
  ').value('.','VARCHAR(MAX)') AS RNote
 from SV000805 SV000805 (nolock)
 Where 
  SV000805.WS_Note_Type='S' 
 --and SV000805.Note_Service_Index='Resolution'
 and SV000805.Service_Call_ID='xyz'

Answer 3

Hi @vsslasd ,

Thank you so much for posting here.

You could try with CAST instead of TRY_CAST.

TRY_CAST returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

CAST returns a error message if the data can’t be converted.

You could also try to use ISNULL(TRY_CAST(...),'') to avoid returning any NULL.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table