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%'

Action is an NTEXT field - this query returns many rows, matching StatusChange in the action text, but the charindex returned is always zero... Any ideas - I need to be able to split this string to tidy up some data?

Answer 1

You've got the parameters to CHARINDEX the wrong way round.

Answer 2

You're swapping parameters:

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

Try:

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