PATINDEX and STUFF @ SQL Server 2005

1- PATINDEX:-

It returns the starting index/position of the first occurrence of the pattern provided. If no match is found then it will return zero.
Syntax:

PATINDEX( '%pattern%', expression )

where
pattern: is the pattern to find.
expression: is the string or usually the column name that will be
searched to find the specified pattern.
--------------------------------------------------------

2- STUFF:-

Deletes a specified length of characters and inserts another set of characters
at a specified starting point.

Syntax:

STUFF ( character_expression , start , length ,
     character_expression )

where:
character_expression:  can be a constant, variable, or column
   of either   character or binary data.
start: is the start location for the deletion and insertion.
 It is  an integer value. If its value is negative or
 longer than character_expression then null string is
 returned.
length: is the length of the character to be deleted.

Example:

SELECT STUFF('abcdefgh', 2, 3, 'SABAH')

Result: aSABAHefgh