Monday, July 9, 2007

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





No comments: