LTRIM, RTRIM, TRIM
These handy little functions trim excess spaces from either the beginning or the end of a string. These
functions should be used when doing any kind of string comparisons. For example, the string "Hello" is
different than "Hello". You could use the LTRIM function to get rid of any leading spaces, so that when you
compared them again, LTRIM("Hello") now equals "Hello".
As explained earlier, the LTRIM function takes one argument. That argument is the string expression
from which you want to trim all the leading spaces. For example
SELECT LTRIM(" Mark")
returns
"Mark"and
SELECT LTRIM("Mark ")still returns
"Mark "
To trim the trailing spaces, you would use the RTRIM function. It works exactly like its counterpart,
except that it trims the trailing spaces instead of the leading ones. For example
SELECT RTRIM("Mark ")would return
"Mark"
The TRIM() function does a little bit more. With just one argument, it will trim both trailing and leading
spaces. For example
SELECT TRIM(" Mark ")returns
" Mark"
The TRIM() function can trim spaces and characters or groups of characters. For example
SELECT TRIM(TRAILING 'XXX' FROM "FileName.XXX")returns
"FileName"
You can also specify leading characters. If you wanted to trim leading and trailing characters, you would
use the keyword BOTH. For example
SELECT TRIM(LEADING 'XXX' FROM "XXXFileName")returns
"FileName"and
SELECT TRIM(BOTH 'XXX' FROM "XXXFileNameXXX")returns
"FileName"SUBSTRING()
The SUBSTRING() function takes two arguments. The first argument is a string expression. The second
argument is a number. The SUBSTRING() function returns a string from the position that is given as the
second argument. For example, if you wanted to return everything after the word Hello in Hello World,
you could use the SUBSTRING() function as follows:
SELECT SUBSTRING("Hello World", 7)
This statement would return the word World.
You could use this function in a variety of ways. It may not be as useful alone as when combined with
other functions, such as the INSTR() function. See Appendix B for more details.