|
Number Functions
You can munipulate retrieved numbers by rounding up to specific precessions or perform extra calculation funtionalities. SQL has several number functions that you can use to manipulate retrieved numbers. The following are list of functions and example of each:
| Function | Description | Example | Result |
| ABS(number) | Returns the absolute value of a number | SELECT ABS
(price-cost)
FROM product; | If (price-cost) = -3.5
Result is 3.5 |
| CEIL(number) | Rounds up the number to next integer | SELECT CEIL
(price-cost)
FROM product
WHERE product_id =1; | If (price-cost) = 3.5
Result is 4 |
| FLOOR(number) | Rounds down the number to next integer | SELECT
FLOOR(price-cost)
FROM product
WHERE product_id =1; | If (price-
cost) = 3.5
Result is 3 |
| MOD(number, divisor) | Returns the remainder of a division |
SELECT MOD
(price,2)
FROM product
WHERE product_id =1; | If price=3
Result is 1 |
| POWER(number, power) | Returns result of number raised specified
power | SELECT POWER(price,2)
FROM product
WHERE product_id
=1; | If price = 3
Result is 9 |
| SIGN(number) | Tests if the number negative or positive
and returns 1 or -
1 | SELECT SIGN(price-cost)
FROM product
WHERE product_id =1; | If (price-cost) = -3.51
Result is -1 |
| ROUND(number, precision) | Returns the a number rounded to the specified
precision | SELECT ROUND(price-cost)
FROM product
WHERE product_id =1; | If (price-cost) = 3.5121212
Result is 3.51 |
| SQRT(number) | Returns the square root of a number
| SELECT SQRT(price)
FROM product
WHERE product_id =1; | If (price) = 4
Result is 2 |
| TRUNC(number, precision) | Returns a number truncated to the specified
precision | SELECT TRUNC(price-cost, 1)
FROM product
WHERE product_id =1; | If (price-cost) = 3.5133
Result is 3.5 |
Concatenation and column alias
You can munipulate the retrieved data by joining two columns or changing the column heading. The default column heading is actual column name. For example, when you select product_id from product, the heading is product_id. We can change the column heading to "product ID" by using following syntax:
SELECT column_name "column_alias"
FROM table_name;
The following example changes and displays column heading of product_id as Product ID, and column heading of name as Product Name.
SELECT product_id "Product ID", name "Product Name"
FROM product;
| This example changes the column headings of product_id and name to proper more understandable column headings |
|
We can also concatenate two or more fields together in select statement. For instance, if we want join first_name and last_name together to make up full_name, we can easily do this using following syntax:
SELECT column_name||column_name FROM table_name; or SELECT CONCAT(column_name,column_name) FROM table_name;. Both methods accomplish same results. The following example retrieves concatenated first_name and last_name with space between and column heading of Full Name:
SELECT first_name ||' '||last_name "Full Name"
FROM employees;
| Using ||, you can concatenate columns or strings with columns. |
|
Single-Row Character Munipulation Functions
There are number of string munipulation functions available for use of character output like the CONCAT we saw. The following are list of those functions with description, example and result:
| Function | Description | Example | Result |
| CONCAT(string1, string2) | Concatenates two strings | SELECT CONCAT
(first_name, last_name)
FROM employees; | If (first_name='John',
last_name='Morgan')
Result is JohnMorgan |
| INITCAP(string) | Returns string with first letter in upper
case | SELECT INITCAP
(first_name)
FROM employees; | If (first_name='john')
Result is John |
| LENGTH(string) | Returns an integer representing the string
length | SELECT LENGTH(first_name)
FROM employees; | If
(first_name='John')
Result is 4 |
| LPAD(string, #,padding_char) | Returns the string padding characters
added to the left | SELECT LPAD
(price, 7,'*')
FROM product
WHERE product_id =1; | If price
=100
Result is ****100 |
| RPAD(string, #,padding_char) | Returns the string padding characters
added to the right | SELECT LPAD
(price, 7,'*')
FROM product
WHERE product_id =1; | If price
=100
Result is 100**** |
LTRIM(string,searchString)
RTRIM(string,searchString)
| Removes characters from left/right of char | SELECT LTRIM
(first_name, 'j')
FROM employees
WHERE employee_id =1; | If
(first_name=' John')
Result is 'john' |
| REPLACE(string,searchString,replacement) | REPLACE returns char with
every occurrence of searchString replaced with replacementString | SELECT
REPLACE(first_name,'J','H')
FROM employees
WHERE employee_id
=1; | If (first_name='John')
Result is 'Hohn' |
| SUBSTR(string,start,length) | Returns substring starting at start and of
specified length | SELECT
SUBSTR(first_name,'1','3')
FROM employees
WHERE employee_id
=1; | If (first_name='John')
Result is 'Joh' |
UPPER(string)
LOWER(string) | Returns string with all upper/lower case
characters | SELECT
UPPER(first_name)
FROM employees
WHERE employee_id
=1; | If (first_name='John')
Result is 'JOHN' |
Single-Row Date Functions
All sql data can manipulated on retrieval or displayed as you desire it to be displayed. The date functions we are descussing next can be used to manipulate date data types. For example, if you want to add few days, months, years to the current date or format the output of the dates retrieved by select statement. This can done using date functions. The following are some list of date functions available in sql:
| Function | Description | Example | Result |
| SYSDATE | Returns current date
| SELECT sysdate
FROM dual; | If
(today ='1/01/2002)
Result is '1-jan-02' |
| ADD_MONTHS(Date, months_to_add) | Returns date retrieved date added to
specified months | SELECT ADD_MONTHS(sysdate,3)
FROM dual; | If
(SYSDATE='1/30/2002)
Result is '4/30/2002' |
| LAST_DAY(Date) | Returns date that is last day of the month
| SELECT LAST_DAY(sysdate)
FROM dual; | If
(SYSDATE='1/20/2002)
Result is '1/31/2002' |
| MONTHS_BETWEEN(Date1, Date2) | Returns months between two dates
specified months | SELECT MONTHS_BETWEEN(TO_DATE('02-02-2002'),SYSDATE)
FROM dual; | If
(SYSDATE='1/1/2002)
Result is 1.0322... |
|
About Clik | abdi_issa@hotmail.com
&nsbp;
