Table 8.2. Commonly Used Text-Manipulation Functions
FUNCTION
Description
LEFT () (or use substring function)
Returns Characters from Left of String
Length () (also datalength () or g ())
Returns the Length of A String
Lower ()
Converts String to Lowercase
Ltrim () (LCase () if using access)
Trims White Space from Left of String
Right () (or use substring function)
Returns Characters from Right Of String
RTRIM ()
Trims White Space from Right Of String
Soundex ()
Returns A String's SoundEx Value
Upper () (ucase () if using access)
Converts String to Uppercase
Select Cust_Name, Cust_Contact
From customer
WHERE SOUNDEX (Cust_Contact) = Soundex ('Michael Green');
Date conversion
Select ORDER_NUM
From Orders
Where datepart (YY, ORDER_DATE) = 2004;
IN Access Use this version:
Select ORDER_NUM
From Orders
Where datepart ('YYYY', ORDER_DATE) = 2004;
Here is the postgreSQL Version That Uses a Similar Function named Date_Part ():
Select ORDER_NUM
From Orders
WHERE DATE_PART ('Year', ORDER_DATE) = 2004;
Mysql Has All Sorts of Date Manipulation Functions, But Not DatePart (). Mysql users can use a function named year from a date:
Select ORDER_NUM
From Orders
WHERE Year (ORDER_DATE) = 2004;
Oracle Has No DatePart () Function Either
Select ORDER_NUM
From Orders
WHERE to_NUMBER (To_Char (ORDER_DATE, 'YY')) = 2004;
Select ORDER_NUM
From Orders
Where order_date between to_date ('01-Jan-2004 ')
And to_date ('31-Dec-2004 ');
In this example, Oracle's to_date () function is used to convert two strings to dates. One contains the date January 1, 2004, and the other contains the date December 31, 2004. A standard BETWEEN operator is used to find all orders between those two dates. It is worth noting that this same code would not work with SQL Server because it does not support the to_date () function. However, if you replaced to_date () with DATEPART (), you could indeed use this type of statement. Table 8.3. Commonly Used Numeric Manipulation Functions
FUNCTION
Description
ABS ()
Returns a Number's Absolute Value
COS ()
Returns the Trigonometric Cosine of a Specified Angle
Exp ()
Returns The Exponential Value of a Specific Number
PI ()
Returns The Value of Pi
Sin ()
Returns the Trigonometric Sine of A Specified Angle
SQRT ()
Returns the Square Root of a Specified Number
Tan ()
Returns the Trigonometric Tangent of a Specified Angle
Refer to your dbms documentation for a list of the supported Mathematical Manipulation functions
