Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - CHARINDEX always returns 0

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?

like image 932
Paddy Avatar asked Feb 19 '10 10:02

Paddy


People also ask

How does Charindex work in SQL Server?

SQL Server CHARINDEX() Function The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search.

How do you find first occurrence of a character in a string SQL?

Searching from the start of a string expression. This example returns the first location of the string is in string This is a string , starting from position 1 (the first character) of This is a string . SELECT CHARINDEX('is', 'This is a string'); Here is the result set.

Can we use Charindex in where clause?

It is a concept that should be avoided whenever possible, and certainly shouldn't be a part of a design where high performance is required. It is better to use LIKE if you must search on the occurrence of a character in a where clause. The probability of survival is inversely proportional to the angle of arrival.

Is Charindex case sensitive?

CHARINDEX is case-sensitive. Use one of the case-conversion functions to locate both uppercase and lowercase instances of a letter or character string.


2 Answers

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

like image 183
AakashM Avatar answered Sep 20 '22 07:09

AakashM


You're swapping parameters:

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

Try:

CHARINDEX(N'StatusChange', CAST([Action] AS NVARCHAR(4000)))
like image 30
Andomar Avatar answered Sep 23 '22 07:09

Andomar