Error Message:
Msg 288, Level 16, State 1, Line 3
The PATINDEX function operates on char, nchar, varchar, nvarchar, text, and ntext data types only.
Severity level:
16.
Description:
This error message appears when you try to non-character values in the string function PATINDEX.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. PATINDEX only works with character data and you need to convert explicitly into one character data type by using CAST() or CONVERT().
Versions:
All versions of SQL Server.
Example(s):
DECLARE @i INT
SET @i = 12345
SELECT PATINDEX(‘%3%’, @i)
Remarks:
In the above example we try to find the first occurence of the character ‘3’ in a value of the type INTEGER. Because an INTEGER value is not allowed in the PATINDEX function, the error is raised. You need to convert the INTEGER value into a string value and then use PATINDEX. Another way might be to use CHARINDEX. However, CHARINDEX has a different scope and might not be applicable for all situations. An interesting observation is that the above example raises two different error messages, depending on which SQL Server version you run the batch. On SQL Server 2000 and below the above error is raised; on SQL Server 2005 however, error 8116 is raised.