SQL Server - CHARINDEX always returns 0

Question

I have the following query:

SELECT 
   CAST([Action] AS NVARCHAR(4000)) AS CastAction, 
   CHARINDEX(CAST([Action] AS NVARCHAR(4000)), N'StatusChange') AS FoundIndex
FROM AuditTrail 
WHERE action LIKE '%StatusChange%'

The action is an NTEXT field - this query returns many rows that correspond to the StatusChange in the text of the action, but the charindex returned is always zero. Any ideas. Do I need to break this line to sort some data?

Answer 1

You have parameters in the CHARINDEXwrong way.

Answer 2

You change the parameters :

Searches expression2 for expression1 and returns its starting position if found.

Try:

CHARINDEX(N'StatusChange', CAST([Action] AS NVARCHAR(4000)))