CODEWITHSUNDEEP
sqlsql-servert-sqlcharindex
Harry Stout
Harry Stout

Reputation: 205

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!

Upvotes: 0

Views: 572

Answers (2)

Harry Stout
Harry Stout

Reputation: 205

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.

Upvotes: 1

igy234
igy234

Reputation: 69

I have just tested your code, the returning charindex in my case is equal to 1. What is more, your query seems to work, at least for the values inserted to table parameter as in example below.

DECLARE @__text_0 NVARCHAR(MAX)= 'Ay, while you live, draw your neck out o'' the collar. Blah, Blah, Blah.';
DECLARE @Paragraphs TABLE(PlainText NVARCHAR(MAX));

INSERT INTO @Paragraphs
VALUES('Ay, while you live, draw your neck out o'' the collar');

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

SELECT CHARINDEX('Ay, while you live, draw your neck out o'' the collar', @__text_0) AS Charindx;

Please try again, the only thing that comes to my mind and could cause problems is an apostrophe sign near "neck out o' the collar".

Upvotes: 0

Related Questions