SQL Server : charindex returning wrong index

Question

I have the following query:

SELECT 
    substring(Content, charindex(N'<img src=''http://tex.', Content), 
                       charindex(N'class=''latex-formula''>', Content)+22) as Img
FROM 
    [Scyk].[dbo].[Posts]
WHERE 
    content LIKE '%<img src=''http://tex.%'

Now I would expect it to give me all <img> tags that I have in database. But instead, it returns something like this:

<img src='http://tex.z-dn.net/?f=x%3Dlog_%7B%5Csqrt%7B2%7D%7D64' id='TexFormula' onerror="texError(this)" title='x=log_{\sqrt{2}}64' alt='x=log_{\sqrt{2}}64' align='absmiddle' class='latex-formula'> to:</p><p>a)

Where the end of the string ends randomly, it can be few characters too many, or it can be plenty characters too many. What is wrong in my query?

Snorre · Accepted Answer · 2013-11-28 21:07:35Z

3

I think the third argument to substring() is the length of the section you want to extract, not the end index. you would have to subtract the first charindex from the second to get the length.

answered Nov 28, 2013 at 21:07

Snorre

9541 gold badge5 silver badges18 bronze badges

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

1 Comment

ojek Over a year ago

Oh. I think you are right, will go check it right away, thanks!

Yosi Dahari · Accepted Answer · 2013-11-28 21:36:02Z

0

The third argument is the length not the index.

edited Nov 28, 2013 at 21:36

answered Nov 28, 2013 at 21:06

Yosi Dahari

7,0395 gold badges26 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL Server : charindex returning wrong index

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related