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
