SQL Server String Functions

SQL Server string functions are mainly used to change the case of string,concatenate string, reverse string,extract various part of strings and perform many other types of string operations.
All functions are Deterministic or Non-deterministic :

• Deterministic functions always return the same result any time they are called with a specific set of input values.
• Non-deterministic functions may return different results each time they are with a specific set of input values.

All built-in string functions ,except for CHARINDEX and PATINDEX , are deterministic. In SQL Server there is several built-in string functions to perform string manipulations.All below functions takes string input value and return a string or numeric values.

ASCII : Function returns the ASCII code from the leftmost character specified character expression.

Syntax : ASCII (character_expression)

CHAR : Convert an int ASCII to character.

Syntax : CHAR (character_expression)

CHARINDEX : Indicates the first position of a character or a character string within another character string. Function takes two arguments. 1st arguments specifies the character whose index is to retrieved and 2nd argument takes as a string from which character index is carried out.

Syntax : CHARINDEX (expression1,expression2 [, start_location] ) 
DIFFERENCE : Returns an Integer value that indicates the difference the SOUNDAX values of two character expressions. 
Syntax : DIFFERENCE(character_expression, character_expression)
LEFT : Returns left part of a string with the specified number of characters counting from left. LEFT function is used to retrieve portions of string.
Syntax : LEFT(character_expression , Integer)
LEN : Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
Syntax : LEN(character_expression)
LOWER :Returns a character expression after converting uppercase character data to lowercase.
Syntax : LOWER(character_expression)
LTRIM : Returns a character expression after it removes leading blanks.
Syntax : LTRIM(character_expression)
NCHAR :Returns the Unicode character with the specified integer code, as defined by the Unicode standard. 
Syntax : NCHAR(Integer_Expression )
PATINDEX : Returns the starting Position of the first occurrence of the pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. 
Syntax : ('%pattern%', expression)
QUOTENAME : Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL server 2005 delimiter identifier.
Syntax : QUOTENAME( 'character_string' [,'qouted_character' ] )
REPLACE : Replaces all occurrences of the second specified string expression in the first string expression with a third expression.
Syntax : REPLACE('string_expression1', 'string_expression2', 'string_expression3')
REPLICATE : Repeats a character expression  for a specified number of times.
Syntax : REPLICATE (character_expression , Integer_expression)
REVERSE : Returns the reverse of a character expression.
Syntax : REVERSE ( character_expression )
RIGHT : Returns the right part of a character string with the specified number of characters.
Syntax : RIGHT( character_expression , integer_expression )
RTRIM : Returns the character string after truncating all trailing blanks.
Syntax : RTRIM ( character_expression )
SOUNDAX : Returns a four character code to evaluate the similarity of two strings.
Syntax : SOUNDAX( character_expression )
SPACE : Returns a string of repeated spaces.
Syntax : SPACE ( integer_expression )
STR : Returns character data converted from numeric data.
Syntax : STR ( float_expression [ , length [ , ] ] )
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 )
SUBSTRING :  Returns part of character , binary , text or image expression.
Syntax : SUBSTRING ( expression , start, length )
UNICODE : Returns the integer value, as defined by the Unicode standard , for the first character of the input string.
Syntax : UNICODE ( 'ncharacter_expression' )
UPPER : Returns a character expression with lowercase character data converted to uppercase.
Syntax : UPPER ( character_expression )