SQL Basics - Using Functions (Text Manipulation)

We really started working with functions in the last post using concatenation. Today we'll discuss one of two types of functions. The first is string manipulation functions, which concatenation is part of. The second category is the aggregate functions, which summarizes values, such as sums and averages. This second function will be discussed in the next post.

STRING MANIPULATION FUNCTIONS

LTRIM

This function is used to remove all spaces to the left of the table entry. For example, if the entry was ' hello', we only want the word 'hello to be returned, and to do this we use LTRIM.

Usage: SELECT LTRIM(colname) From .....
________________________________________

RTRIM

This function is used to remove all spaces to the right of the table entry. For example, if the entry was 'hello ', we only want the word 'hello to be returned, and to do this we use RTRIM

Usage: SELECT RTRIM(colname) From .....
___________________________________

LEFT

This function is used to return a specified number of characters starting at the left of the table entry. This is useful if a column of Zip Codes are saved in 00000-0000 (for you USA'ers) format and we only want the first five digits. For this we use LEFT

Usage: SELECT LEFT(colname,5) From .....
___________________________________

RIGHT

This function is used to return a specified number of characters starting at the right of the table entry. If we wanted to get the last 4 digits of a Social Security number (also for you USA'ers), you would use the RIGHT function.
Usage: SELECT RIGHT(colname,4) From .....
___________________________________

LEN

This function simply gets the length of the specified entry. For example, to get the lenght of a user's password, we use LEN.
Usage: SELECT LEN(pass_word) From .....