Searching or matching text in string in MS SQL Server

Funcation CONTAINS match pattern which pass in second parameter.

Function PATINDEX works very similar to function CHARINDEX. We can use searching patter in PATINDIEX unlike CHARINDEX.

Syntax and example given below.

 --Pattern matching  

SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'rk')

SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'"rk*"')

SELECT PATINDEX('%rk%', [name]) as idx FROM tb1      --It return integer index

--like indexOf() (programming language function)

SELECT CHARINDEX('rk', [name])  as idx FROM tb1      --It return integer index

--like lastIndexOf() (programming language function)

SELECT CHARINDEX('rk', REVERSE([name])) FROM tb1     --It return integer index