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?
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.
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.
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.
CHARINDEX is case-sensitive. Use one of the case-conversion functions to locate both uppercase and lowercase instances of a letter or character string.
You've got the parameters to CHARINDEX
the wrong way around.
You're swapping parameters:
Searches expression2 for expression1 and returns its starting position if found.
Try:
CHARINDEX(N'StatusChange', CAST([Action] AS NVARCHAR(4000)))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With