SQL Server Contains Equivalent

Many Oracle developers trying to find the SQL Server function compatible with their Contains clause will most likely end up on this page. Therefore, this page title is directed towards the Oracle developer rather than for the SQL Server’s Contains function which is used for full-text searching.

The most similar function to Oracle’s contains is charindex.

The usage is similar except the first two parameters are reversed:

[cc lang=”sql”]
DECLARE @BaseString varchar(max)

SET @BaseString = ‘Quick Brown Fox’

SELECT CHARINDEX(‘Brown’, @BaseString, 1)
[/cc]

The result returns the integer value 7, indicating the character position for the string ‘grand’ within the @BaseString variable. The last parameter allows a start position to be specified.