SQL Server: basic functions (part 1) - Manipulating strings

Tweet

In Sql Server, functions are special commands that perform some kind of operation during the execution of queries. They are used to manipulate results, refine selections, filter data and so on. Some of these functions are really handy and should be used in the query itself because, manipulating data at query level is surely better than do it afterwards.
I would like to gather some functions in some posts, just as quick reference with examples. I understand that the functions here examined are just a tiny selection of all the available commands, however we need to start from somewhere, don't we?
The functions we will see in this post are:

• right, left and substring,
• rtrim and ltrim,
• len.

Right, Left and Substring

These functions are used to take only a part of a string.
The Right function returns the right part of a character string with a specified number of characters.

SELECT RIGHT(FirstName, 5) AS 'First Name' FROM PersonTable

The result of the above query will be the list of the first 5 characters of the FirstName column starting from right (the end of the string).
The same effect will be produced by the Left function but starting from the left (the beginning of the string):

SELECT LEFT(FirstName, 5) AS 'First Name' FROM PersonTable

The result will be the list of the first 5 characters of the FirstName column starting from left (the beginning of the string).
The Substring function returns part of a string and requires 3 parameters: the string, the starting point and the length. The start indicates where the substring begins and the length indicates how many characters of the string will be returned (can't be negative).

SELECT SUBSTRING(FirstName, 3, 3) AS SubExample FROM PersonTable

Running the above table will return a column named SubExample with 3 characters starting from the third character of the FirstName column.

RTrim and LTrim

RTrim and LTrim are string manipulation functions used to remove respectively trailing and leading spaces.
If you have PersonTable like:

PersId	FirstName
1	John

where 'John' is actually 'John      ' (notice the trailing spaces), then you can use RTrim to remove those extra spaces.

SELECT RTRIM(FirstName) AS 'First Name' FROM PersonTable

will return 'John' without the trailing spaces.
In the same way, LTrim will work for the leading spaces. If your string is like '       John', then:

SELECT LTRIM(FirstName) AS 'First Name' FROM PersonTable

will return 'John'.
SQL Server has no TRIM function (removing trailing and leading spaces altogether). In any case, you can use the 2 functions in conjunction:

SELECT LTRIM(RTRIM(FirstName)) AS 'First Name' FROM PersonTable

Len

The Len function returns the number of characters of a given string. It is actually the length of the string.
For example, your string is 'John Doe'.

SELECT LEN(Name) AS Length, Name FROM PersonTable

will return a recordset like:

Length	Name
8	John Doe

where '8' is the number of characters of 'John Doe'. Notice that spaces are counted.

Ok for now. Next time we will see the CASE function.

Do share your thoughts, please.