8 T-SQL String Functions

Working with strings is one of the most common T-SQL coding tasks, whether you’re trimming blanks off a string value for display or concatenating two strings together.

Use the LEN function to determine a source string’s length. It takes a single parameter containing a string expression.

SELECT LEN('This is string') AS Length

Length
-----------
14

2. LEFT

You use the LEFT function to return a specified number of characters from a string’s left side. It takes two parameters: the source string expression and an integer indicating the number of characters to return.

SELECT LEFT ('SQL Server 2008', 3) As SQL

SQL
----
SQL

3. RIGHT

The RIGHT function returns a specified number of characters from a string’s right side. It also accepts a string expression and an integer.

SELECT RIGHT ('SQL Server 2008',4) As Release

Release
-------
2008

4. LTRIM

The LTRIM function removes leading blanks from a string. It takes a single string parameter.

DECLARE @myString varchar(40)= ' Get rid of five leading blanks'SELECT 'The new string: ' + LTRIM(@myString) As Example

Example
---------------------------------------------------------
The new string: Get rid of five leading blanks

5. RTRIM

As you might guess, the RTRIM function removes trailing blanks from a string. It takes a single string parameter.

DECLARE @myString varchar(40) ='Get rid of five trailing blanks 'SELECT 'The new string: '+ RTRIM (@myString) As Example

Example
---------------------------------------------------------
The new string: Get rid of five trailing blanks

6. SUBSTRING

SUBSTRING function returns a specified portion of a string. The first parameter is the source string, the second indicates the start position in the source string, and the third indicates the length to return.

SELECT 'Otey' AS \[Last Name\],SUBSTRING('Michael', 1, 1)As Initial

Last Name Initial
--------- -------
Otey M

7. REPLACE

This function replaces all the instances of a specified source string within a target string. The first parameter is the source string expression, next is the search string, and last is the replacement string.

SELECT REPLACE('SQL Server 2005','2005','2008') As \[Replace Example\]

Replace Example
------------------
SQL Server 2008

8. STUFF

Use the STUFF function to insert one string within another string. The first parameter is the source string expression. Next is the insertion point, followed by the number of characters to delete, and finally the string that will be inserted into the source string.

SELECT STUFF('SQL Services',5, 8, 'Server') As \[Stuff Example\]

Stuff Example
-------------
SQL Server

Learn more about string functions from the MSDN Library.