Search code examples
sql-servercharindex

SQL Server : index of multiple characters

I recently found out that my links stored in the database do not open in new tabs (no target="_blank"). I made this script that adds it in.

My problem is that if the Preamble column has multiple links, only the first one will be updated with target="_blank", how can I run this for all links in the column?

SELECT 
    STUFF(Preamble, CHARINDEX('>', Preamble, PATINDEX('%<a href%', Preamble)) - 1, 1, '" target="_blank"')
FROM 
    QuestionContainer
WHERE 
    Preamble LIKE '%<a href%'

The Preamble column contains other HTML markup and other text.

Update

So for some reason I was trying to add the target="_blank" to the end of the anchor tag, instead I can just add it to the start.
A simple REPLACE(Preamble, '%<a href%', '<a target="_blank" href') will hit all occurrences in a row and solve my problem. - Thanks Allan S. Hansen

Solution

  • Try with REPLACE function:

    DECLARE @s NVARCHAR(MAX) = '<a href="some1"><a href="some2">'
SELECT REPLACE(@s, '>', ' target="_blank">') AS Link

    Output:

    Link
<a href="some1" target="_blank"><a href="some2" target="_blank">

    Apply to your statement:

    SELECT REPLACE(Preamble, '>', ' target="_blank">')
FROM QuestionContainer
WHERE Preamble like '%<a href%'

    EDIT, according to @Allan comment:

    DECLARE @s NVARCHAR(MAX) = '<a href="some1"></a><a href="some2"></a>'
SELECT  REPLACE(REPLACE(@s, '>', ' target="_blank">'), '</a target="_blank">', '</a>') AS Link

    EDIT 2: if you have different type of tags then it makes sense to put replacement string in the beginnning:

    DECLARE @s NVARCHAR(MAX) = '<a href="some1"></a><a href="some2"></a><b>'
SELECT  REPLACE(@s, '<a ', '<a target="_blank" ') AS Link

    Output:

    Link
<a target="_blank" href="some1"></a><a target="_blank" href="some2"></a><b>