SQL Server CONTAINSSTR Function

Updated 2023-11-10 16:30:09.090000

Description

Use the scalar function CONTAINSSTR to determine if a string contains a particular value.

Syntax

SELECT [westclintech].[wct].[CONTAINSSTR] (
  <@Text, nvarchar(max),>
 ,<@Value, nvarchar(max),>
 ,<@CaseSensitive, bit,>)

Arguments

@Text

is the text to be searched. The @Text argument can be of data types that are implicitly convertible to nvarchar or ntext.

@Value

is the value to search for in @Text. The @Value argument can be of data types that are implicitly convertible to nvarchar or ntext.

@CaseSensitive

declares the search as being either case sensitive or case insensitive, regardless of collation. The @CaseSenstive argument must be of data types that are implicitly convertible to bit.

Return Type

bit

Remarks

CONTAINSSTR does not support the use of wildcards.

CONTAINSSTR supports case sensitive searches. @CaseSensitive = 1, makes the search case sensitive.

Examples

select wct.CONTAINSSTR('four score', 'OU', 1);

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0"}]}

select wct.CONTAINSSTR('four score', 'OU', 0);

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"1"}]}

select CHARINDEX('OU', 'four score');

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"2"}]}