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,
The Right function returns the right part of a character string with a specified number of characters.
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).
SELECT RIGHT(FirstName, 5) AS 'First Name' FROM PersonTable
The same effect will be produced by the Left function but starting from the left (the beginning of the string):
The result will be the list of the first 5 characters of the FirstName column starting from left (the beginning of the string).
SELECT LEFT(FirstName, 5) AS 'First Name' FROM PersonTable
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).
Running the above table will return a column named SubExample with 3 characters starting from the third character of the FirstName column.
SELECT SUBSTRING(FirstName, 3, 3) AS SubExample FROM PersonTable
RTrim and LTrimRTrim and LTrim are string manipulation functions used to remove respectively trailing and leading spaces.
If you have PersonTable like:
where 'John' is actually 'John ' (notice the trailing spaces), then you can use RTrim to remove those extra spaces.
will return 'John' without the trailing spaces.
SELECT RTRIM(FirstName) AS 'First Name' FROM PersonTable
In the same way, LTrim will work for the leading spaces. If your string is like ' John', then:
will return 'John'.
SELECT LTRIM(FirstName) AS 'First Name' FROM PersonTable
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
LenThe 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'.
will return a recordset like:
SELECT LEN(Name) AS Length, Name FROM PersonTable
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.