Replacing Every Other Occurrence of String

Question

Hi,

Running SQL Server 2017. I have the following HTML snippet contained in a varchar(MAX) variable:

<tr>
    <td>Cell 1</td>
    <td>Cell 2</td>
</tr>
<tr>
    <td>Cell 1</td>
    <td>Cell 2</td>
</tr>
<tr>
    <td>Cell 1</td>
    <td>Cell 2</td>
</tr>

How would I go about replacing every other occurrence of <tr> with something else?

Thanks!
B

Answer 1

@VDT-7677 ,

(1) As Erland correctly mentioned, the best course of actions is to use CSS for styling.
Please take a look how to do it correctly in my answer here: html-code-inside-sql

(2) If you still need an embedded styling, please see below. Unfortunately, for no reason this Web site doesn't allow me to paste XQuery code. Sorry, I had to make a screen shot for that reason. Alas, even to embed Image functionality is currently broken here.

If you wish, you can ask the same question on the stackoverflow.com
And I will gladly provide you a full answer.

SQL

DECLARE @xml XML  
 , @style VARCHAR(100) = 'background: #FFE5B4; font-weight: 800;'  
 , @tbody NVARCHAR(MAX) =   
N'<tbody>  
 <tr>  
 <td>Cell 1</td>  
 <td>Cell 2</td>  
 </tr>  
 <tr>  
 <td>Cell 1</td>  
 <td>Cell 2</td>  
 </tr>  
 <tr>  
 <td>Cell 1</td>  
 <td>Cell 2</td>  
 </tr>  
</tbody>';  

P.S. Please connect with me on LinkedIn or Skype.

Answer 2

You should to this in your style-sheet and leave the tr tags alone. See https://www.w3schools.com/howto/howto_css_table_zebra.asp.

Answer 3

To do it in T-SQL, you would use a string-splitter that splits on the <TR> string to get all the elements. I have a shorter-article on my web site with such functions: http://www.sommarskog.se/arrays-in-sql.html

Then you would build back the string like this:

'<TR ' + iif(listpos % 2 = 1, '>', ' STYLE="background-colour: ...">' + str

You can to this with string_agg or FOR XML PATH, which I also discuss in the above mentioned article.

However, you may find that this works no better. I recall that I wrote some SQL code that produced HTML, and I tried to add some nice formatting, but Outlook dropped of that on the floor.