SQL Server REPLACE Function Fails with HTML from Database

Data and Databases
1

I am facing an issue with the REPLACE function in SQL Server when dealing with HTML data stored in my database. It works fine when I use a hardcoded string, but doesn’t seem to function correctly when I retrieve the content from a column in a table.

Example where it works using a hardcoded string:

DECLARE @htmlContent varchar(MAX) = '<div>
    <section>
        <article>
            <div>
                <div class="btn-wrapper" style="width: 200px; height: 40px; margin: auto;">
                    <button type="button" style="background: __ThemeColor__; border-radius: 5px; font-family: Arial, sans-serif; color: __TextColor__; padding: 12px 30px;">
                        <span style="color: __TextColor__; font-weight: bold;">
                            <a style="color: __TextColor__; text-decoration: none;" href="__ActionURL__">Accept</a>
                        </span>
                    </button>
                </div>
            </div>
        </article>
    </section>
</div>'

SELECT @htmlContent = REPLACE(@htmlContent,
    '<button type="button" style="background: __ThemeColor__; border-radius: 5px; font-family: Arial, sans-serif; color: __TextColor__; padding: 12px 30px;">
        <span style="color: __TextColor__; font-weight: bold;">
            <a style="color: __TextColor__; text-decoration: none;" href="__ActionURL__">Accept</a>
        </span>
    </button>',
    '<button type="button" style="background: __ThemeColor__; border-radius: 5px; font-family: Arial, sans-serif; color: __TextColor__; padding: 12px 30px;">
        <span style="color: __TextColor__; font-weight: bold;">
            <a style="color: __TextColor__; text-decoration: none;" href="__ActionURL__">Accept</a>
        </span>
    </button>
    <div style="display: __ShowOption__;">
        <p style="font-family: Arial, sans-serif; font-size: 12px; color: #888; text-align: center; margin-top: 10px;">
            <a style="color: #666; text-decoration: underline;" href="__DeclineURL__">decline.option.text</a>
        </p>
    </div>')

PRINT @htmlContent

Example that does not work using data from the database:

DECLARE @htmlContent VARCHAR(MAX) = (SELECT TOP 1 HtmlContent 
                                        FROM [EmailTemplates] 
                                        WHERE HtmlContent LIKE '%class="btn-wrapper"%')

SELECT @htmlContent = REPLACE(@htmlContent,
    '<button type="button" style="background: __ThemeColor__; border-radius: 5px; font-family: Arial, sans-serif; color: __TextColor__; padding: 12px 30px;">
        <span style="color: __TextColor__; font-weight: bold;">
            <a style="color: __TextColor__; text-decoration: none;" href="__ActionURL__">Accept</a>
        </span>
    </button>',
    '<button type="button" style="background: __ThemeColor__; border-radius: 5px; font-family: Arial, sans-serif; color: __TextColor__; padding: 12px 30px;">
        <span style="color: __TextColor__; font-weight: bold;">
            <a style="color: __TextColor__; text-decoration: none;" href="__ActionURL__">Accept</a>
        </span>
    </button>
    <div style="display: __ShowOption__;">
        <p style="font-family: Arial, sans-serif; font-size: 12px; color: #888; text-align: center; margin-top: 10px;">
            <a style="color: #666; text-decoration: underline;" href="__DeclineURL__">decline.option.text</a>
        </p>
    </div>')

PRINT @htmlContent

The REPLACE function performs as expected with the hardcoded content, but it does not yield results when applied to the data fetched from the database. Does anyone know the reason for this discrepancy?

2

that’s a good point! have you also looked into the collations? they can affect string comparisons in unexpected ways. it might also be useful to print out the actual content from the db to see if there’s something off about it.

3

Sounds like whitespace or encoding issues. Try LTRIM/RTRIM on the database content first - there might be hidden characters breaking the exact match. Also check if your DB column has different line endings (CR/LF vs LF) than your hardcoded string.

4

Had this exact issue on a recent email template migration. Turned out to be character encoding differences - my hardcoded strings and database content looked identical when printed, but the database had Unicode characters that appeared like normal spaces but with different byte values. I used DATALENGTH() to compare the actual byte lengths of matching substrings from both sources. When I found mismatches, I switched to PATINDEX() to find the general pattern first, then pulled out and replaced sections using SUBSTRING() with string concatenation. Worked every time, even with those weird encoding variations that broke exact string matching.