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

vsslasd 556 Reputation points
2020-12-02T20:05:08.107+00:00

Declare @Table table (SCID varchar(17), NoteValue varchar(max))
Insert into @Table (SCID, NoteValue)
Values
('121','asdfasdf'),
('223251','any text can go here, and line feeds, etc. then somewhere in the text there will be one or more urls like this http://abc.com and then another one https://micr.com/asdfsdafsd/asdfasdf and yet another link here https://123.com/asdf/asf34/asdfasdf/asdfasd/asdfasdf and perhaps more....'),
('223251','and here is another http://asdf.com/asdfasdf')

Select * from @Table

--The Issue:

--The output we want then is to wrap a blue bold font around any http:// or https:// lines starting at the "h" and ending at the first white space, in order to turn these hypertexts blue:

e.g.:
<font color = 'blue'><b>

</b></font>

--The End Result should be:

Select * from @Table

121 asdfasdf
223251 any text can go here, and line feeds, etc. then somewhere in the text there will be one or more urls like this <font color = 'blue'><b> http://abc.com</b></font> and then another one <font color = 'blue'><b> https://micr.com/asdfsdafsd/asdfasdf </b></font> and yet another link here <font color = 'blue'><b> https://123.com/asdf/asf34/asdfasdf/asdfasd/asdfasdf </b></font> and perhaps more....
223251 and here is another <font color = 'blue'><b> http://asdf.com/asdfasdf</b></font>

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,926 Reputation points
    2020-12-02T20:57:55.257+00:00

    Here is a solution for SQL Server 2017 onwards.

    SQL

    DECLARE @tbl TABLE (SCID VARCHAR(17), NoteValue VARCHAR(MAX));
    INSERT INTO @tbl (SCID, NoteValue) VALUES
    ('121', 'asdfasdf'),
    ('223251',
     'any text can go here, and line feeds, etc. then somewhere in the text there will be one or more urls like this http://abc.com and then another one https://micr.com/asdfsdafsd/asdfasdf and yet another link here https://123.com/asdf/asf34/asdfasdf/asdfasd/asdfasdf and perhaps more....'),
    ('223251', 'and here is another http://asdf.com/asdfasdf');
    
    SELECT *
       , (SELECT STRING_AGG(IIF(LEFT(value, 7) IN ('http://', 'https:/'),
     CONCAT('<font color = "blue"><b>', value, '</b></font>'),value), SPACE(1))
                FROM STRING_SPLIT(NoteValue, SPACE(1))
                ) AS NoteValueModified
    FROM @tbl;
    

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,926 Reputation points
    2020-12-02T22:06:34.027+00:00

    Another solution that is an answer to Viorel's concerns.
    Trusted XML, XQuery, and FLWOR expression.

    SQL

    DECLARE @tbl TABLE (SCID VARCHAR(17), NoteValue VARCHAR(MAX));
    INSERT INTO @tbl (SCID, NoteValue) VALUES
    ('121', 'asdfasdf'),
    ('223251',
     'any text can go here, and line feeds, etc. then somewhere in the text there will be one or more urls like this http://abc.com and then another one https://micr.com/asdfsdafsd/asdfasdf and yet another link here https://123.com/asdf/asf34/asdfasdf/asdfasd/asdfasdf and perhaps more....'),
    ('223251', 'and here is another http://asdf.com/asdfasdf');
    
    -- Method #2
    DECLARE @separator CHAR(1) = SPACE(1);
    
    SELECT *
        , TRY_CAST('<root><r>' + 
            REPLACE(NoteValue, @separator, '</r><r>') + 
            '</r></root>' AS XML).query('
     for $x in /root/r
     return if (substring(($x/text())[1],1,4) eq "http") then
     concat("<font color=""blue""><b>", ($x/text())[1], "</b></font>")
     else data($x)
     ').value('.','VARCHAR(MAX)') AS NoteValueModified
    FROM @tbl;
    
    1 person found this answer helpful.