PatIndex

PatIndex( ‘%pattern%’, char_expression [using {bytes | chars | characters} )

Returns the starting position of the first match of ‘pattern’ in char_expression. If no match is found, 0 is returned. If the using clause is omitted, or if ‘chars’ or ‘characters’ is chosen, starting position N means the Nth character. If ‘using bytes’ is chosen, starting position N means the Nth byte.

Example

declare @uvc univarchar(30)

set @uvc = N'abcde'

select PatIndex( '%cd%', @uvc, using bytes ) as Nth_byte

select PatIndex( '%cd%', @uvc ) as Nth_char

Differences

MS SQL Server does not allow the using clause. On MS SQL Server, PatIndex always returns the position of the pattern counted in characters.

Furthermore, MS SQL Server handles NULL values differently.