String Functions @ SQL Server 2005

There are number of scalar function to manipulate the strings in SQL server. I will just discuss the most common used scalar functions available for string manipulation:

1- REVERSE
This scalar function is used to reverse the character string provided. The input parameter may be constant string, variable or column name of character or binary type.
EXAMPLE:- SELECT REVERSE('SABAH') , REVERSE('IRFAN')
RESULT:- HABAS NAFRI

2- LEN
It returns the number of characters in the input parameter provided. Input parameter may be constant char string, variable or column name. Please note it will not count the trailing spaces.
EXAMPLE:- SELECT LEN('SABAH U DIN') , LEN('IRFAN ')
RESULT:- 11 5

3- SPACE
It returns the number of spaces according to the integer parameter provided as input. It takes integer_expression as input. if the integer expression is negative then it returns null string.
EXAMPLE:- SELECT 'Sabah' +SPACE(10)+'Irfan'
RESULT:- Sabah Irfan

4- REPLACE
This function has three parameters all of character type. It finds the 2nd parameter_string from 1st_parameter string and replaces all occurrence with the 3rd parameter_string.
EXAMPLE:- SELECT REPLACE('abcdefghicde','cde','xxx')
RESULT:- abxxxfghixxx

5- ASCII
It returns the integer ASCII code of the left most character of the input string.
EXAMPLE:- SELECT ASCII('A'),ASCII('ABCDEF')
RESULT:- 65 65

6- CHAR
It converts the integer ASCII code into char.
EXAMPLE:- SELECT CHAR(65),CHAR(78)
RESULT:- A N

7- LOWER
It returns the lower case character string after converting the upper case characters present into the input string.

EXAMPLE:- SELECT LOWER('SAbah U Din')
RESULT:- sabah u din

8- UPPER
It returns the upper case character string after converting the lower case characters present into the input string.

EXAMPLE:- SELECT UPPER('SAbah U Din')
RESULT:- SABAH U DIN

9- LEFT
It returns the left part of the string with the number of characters provided.
EXAMPLE:- SELECT LEFT('Sabah-u-Din',7)
RESULT:- Sabah-u

10- RIGHT
It returns the right part of the character string with the number/count of characters provided in its 2nd parameter.
EXAMPLE:- SELECT RIGHT('Sabah-u-Din',7)
RESULT:- h-u-Din

11- RTRIM
It returns a character string after truncating all trailing blanks(blanks at the end of string).
EXAMPLE:- SELECT RTRIM('Sabah ')
RESULT:- Sabah

12- LTRIM
It returns a character string after truncating all leading blanks(blanks at the start of string).
EXAMPLE:- SELECT LTRIM(' Sabah')
RESULT:- Sabah

13- CHARINDEX
It returns the starting position of the specified expression in a character string. Its syntax is

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

 EXAMPLE:-  SELECT CHARINDEX('r','rSaba-u-din-irfan',3)
 RESULT:- 14

NOTE: There are a couple of more scalar functions available in SQL Server but the above
are the mostly used ones.