SQL Server : index of multiple characters

Question

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

Would something like update questioncontainer set preamble = replace(preamble, '<a href', '<a href target="_blank" ') not do it? (of course this doesn't take into account those where you've already done it, will need to replace those back first :)) Otherwise you'll need to write a loop / function to split your column and re-work your indexes — Allan S. Hansen
– Allan S. Hansen, Commented Mar 20, 2015 at 7:25
This. But only if none of them have it, otherwise you would have to exclude the ones containing "_blank". — M.Stoop
– M.Stoop, Commented Mar 20, 2015 at 7:27
@AllanS.Hansen The Preamble column contains other HTML markup and other text. — Mathemats
– Mathemats, Commented Mar 20, 2015 at 7:29
That's why you need to replace the <a href with to ensure it doesn't trigger on other html — Allan S. Hansen
– Allan S. Hansen, Commented Mar 20, 2015 at 7:30
REPLACE('<a href=some.website/default.asp">Department’s Purchasing and Procurement Instructions</a>', '<a href', '<a target="_blank" href'); seems to work and then it's just replacing the actual text with your column and updating — Allan S. Hansen
– Allan S. Hansen, Commented Mar 20, 2015 at 7:38

Giorgi Nakeuri · Accepted Answer · 2015-03-20 07:38:56Z

2

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>

edited Mar 20, 2015 at 7:38

answered Mar 20, 2015 at 7:27

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Allan S. Hansen Over a year ago

hyperlinks often end with a </a>

Mathemats Over a year ago

@GiorgiNakeuri This makes sense, but I have ~100 rows, I will still have to copy paste the links into the DECLARE which is what I want to avoid.

Collectives™ on Stack Overflow

SQL Server : index of multiple characters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related