SQL Server PatIndex

 Returns the starting position of the first occurrence of a the specified pattern in the specified expression.

Parameters:

1. pattern. A character expression the contains the sequence to be found. Limited to A maximum length of

8000 chars. Wildcards (%, _) can be used in the pattern. If the pattern does not start with a wildcard, it may only match whatever is in the beginning of the expression. If it doesn't end with a wildcard, it may only match whatever is in the end of the expression.

2. expression. Any string data type.

Example:

SELECT PATINDEX('%ter%', 'interesting') -- Returns 3.
SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3.
SELECT PATINDEX('ter%', 'interesting') -- Returns 0, since 'ter' is not at the start.
SELECT PATINDEX('inter%', 'interesting') -- Returns 1.
SELECT PATINDEX('%ing', 'interesting') -- Returns 9.

s

SQL Server Replace()

 Returns a string (varchar or nvarchar) where all occurrences of a specified sub string is replaced with another substring.

Parameters:

1. string expression. This is the string that would be searched. It can be a character or binary data type.

2. pattern. This is the sub string that would be replaced. It can be a character or binary data type. The pattern argument cannot be an empty string.

3. replacement. This is the sub string that would replace the pattern sub string. It can be a character or binary data.

SELECT REPLACE('This is my string', 'is', 'XX') -- Returns 'ThXX XX my string'.

Notes:

• If string expression is not of type varchar(max) or nvarchar(max), the replace function truncates the return value at 8,000 chars.
• Return data type depends on input data types - returns nvarchar if one of the input values is nvarchar, or varchar otherwise.
• Return NULL if any of the input parameters is NULL