Database Reference
In-Depth Information
Another very useful function pair is LTRIM and RTRIM. These guys trim spaces off the
left or right side of the column or expression passed in. LTRIM trims the left side, where-
as RTRIM does the right side. This becomes important when working with columns of the
data type c h a r. So you can visualize this, let's plug in values instead of columns.
SELECT RTRIM ('Hello ') + ' ' + 'Every' + ' ' + 'Body';
OUTPUT
- - - - - - - - - - - - - - - - - - - -
Hello Every Body
SUBSTRING (or SUBSTR)
SUBSTRING retrieves the requested number of characters but takes a starting position as
a parameter. This allows users to retrieve a portion of a string from the middle. To demon-
strate this, we're going to use the PhoneNumber column from the Customer table. The fol-
lowing query will break the phone number into its respective parts and add formatting.
SELECT SUBSTR ('Hello Every Body',2,4) FROM dual;
Output: ello
LENGTH
LENGTH is a function that can be used to find the length of a column of
expression.
SELECT LENGTH ( 'Hello' ) FROM dual;
REPLACE
EPLACE is another handy string function. It can be used to replace a portion of a string
with another value. You use this function by specifying, first, the string that contains the
value you'd like to replace. Then specify the portion that needs replacing.
SELECT Phone, REPLACE(Phone, '317', '111') FROM staff;
DAY, MONTH, and YEAR
DAY, MONTH, or YEAR functions provide the same results as using the DATEPART
function for the day, month, or year, respectively. The following example shows the syntax.
SELECT DAY('6/21/2003') AS Day,
MONTH('6/21/2002') AS Month,
YEAR('6/21/2002') AS Year;
