Search code examples
sqlsql-servert-sqlcharindex

Why is CHARINDEX returning 0?

I am unable to see why CHARINDEX keeps returning 0 for the following query:

DECLARE @__text_0 nvarchar(max) = 'Ay, while you live, draw your neck out o'' the collar. Blah, Blah, Blah.';

SELECT [p].[PlainText]
FROM Shakespeare_Works.dbo.Paragraphs AS [p]
WHERE (CHARINDEX([p].[PlainText], @__text_0) > 0)

One of the Paragraphs' PlainText cells contains:

Ay, while you live, draw your neck out o' the collar

and PlainText is nvarchar(max).

What could possibly be going wrong?

Thanks!

Solution

  • I investigated the ASCII of the data contained in the cell and it showed that at the end of each cell was a 0A ASCII char.