String Functions
These built-in functions are used for string manipulations.
Following are the common string functions:
UPPER: Converts an expression from lower case to upper case.
SYNTAX
UPPER ( expression )
EXAMPLE
Query:
SELECT UPPER('SQL Server 2008') AS [UPPER]
Output :
UPPER
SQL SERVER 2008
LOWER: Converts an expression from upper case to lower case.
SYNTAX
LOWER ( expression )
EXAMPLE
Query:
SELECT LOWER('SQL Server 2008') AS [LOWER]
Output :
LOWER
sql server 2008
SUBSTRING: Extracts part of an expression.
SYNTAX
SUBSTRING ( expression, start index, length )
EXAMPLE
Query:
SELECT SUBSTRING('SQL Server 2008', 5, 6) AS [SUBSTRING]
Output :
SUBSTRING
Server
REPLACE: Replaces part of an expression with another value.
SYNTAX
REPLACE ( expression, search string, replacement string )
EXAMPLE
Query:
SELECT REPLACE('SQL Server 2008', '2008', '2012') AS [REPLACE]
Output :
REPLACE
SQL Server 2012
LEFT: Extracts specified number of characters from left side of an expression.
SYNTAX
LEFT ( expression, length )
EXAMPLE
Query:
SELECT LEFT('SQL Server 2008', 3) AS [LEFT]
Output :
LEFT
SQL
RIGHT: Extracts specified number of characters from right side of an expression.
SYNTAX
RIGHT ( expression, length )
EXAMPLE
Query:
SELECT RIGHT('SQL Server 2008', 4) AS [RIGHT]
Output :
RIGHT
2008
REVERSE: Reverses an expression.
SYNTAX
REVERSE ( expression )
EXAMPLE
Query:
SELECT REVERSE('SQL Server 2008') AS [REVERSE]
Output :
REVERSE
8002 revreS LQS
LEN: Returns number of characters in an expression.
SYNTAX
LEN ( expression )
EXAMPLE
Query:
SELECT LEN('SQL Server 2008') AS [LEN]
Output :
LEN
15
LTRIM: Returns expression after removing leading blank spaces.
SYNTAX
LTRIM ( expression )
EXAMPLE
Query:
SELECT LTRIM('          SQL Server 2008') AS [LTRIM]
Output :
LTRIM
SQL Server 2008
RTRIM: Returns expression after removing trailing blank spaces.
SYNTAX
RTRIM ( expression )
EXAMPLE
Query:
SELECT RTRIM('SQL Server 2008          ') AS [RTRIM]
Output :
RTRIM
SQL Server 2008
CHARINDEX: Returns the starting position of an expression within another expression. The function includes an optional start location parameter, which can be provided as an argument to define the point in the second expression at which the comparison should begin.
SYNTAX
CHARINDEX ( expression to find, expression to search [,optional start index] )
EXAMPLE
Query:
SELECT CHARINDEX('Server', 'SQL Server 2008', 3) AS [CHARINDEX]
Output :
CHARINDEX
5
PATINDEX: Returns the starting position of an expression within another expression. It is similar to CHARINDEX, but instead of searching for an exact match, it searches for a pattern. So the expression to search must include wildcard character “%”. Unlike CHARINDEX, PATINDEX does not allow start location.
SYNTAX
PATINDEX ( %pattern%, expression to search )
EXAMPLE
Query:
SELECT PATINDEX('%Ser%', 'SQL Server 2008') AS [PATINDEX]
Output :
PATINDEX
5