SQL Functions

 

Arithmetic & characters comparison

 

Arithmetic Function

Functions Description

 

abs()    This SQL ABS() returns the absolute value of a number passed as argument.

 

ceil()   This SQL CEIL() will rounded up any positive or negative decimal value within the function upwards.

 

floor()   The SQL FLOOR() rounded up any positive or negative decimal value down to the next least integer value.

 

exp() The SQL EXP() returns e raised to the n-th power(n is the numeric expression), where e is the base of natural algorithm and the value of e is approximately 2.71828183

 

ln() The SQL LN() function returns the natural logarithm of n, where n is greater than 0 and its base is a number equal to approximately 2.71828183.

 

mod() This SQL MOD() function returns the remainder from a division.

 

power() This SQL POWER() function returns the value of a number raised to another, where both of the numbers are passed as arguments.

 

sqrt() The SQL SQRT() returns the square root of given value in the argument.

 

 

CHARACTER FUNCTIONS

ORACLE SQL SERVER DB2  
ASCII ASCII   ASCII returns the decimal representation in the

data base character Set Of the first character Of

char

CHR CHAR CHAR CHR returns the character having the binary

equivalent to n in either the database character

set or the national character set.

CONCAT +(String concatenation)   CONCAT returns char1 concatenated with char2.

Both char1 and char2 can be any of the

datatypes CHAR, vARCHAR2, NCHAR,

NvARCHAR2, CLOB, or NCLOB. The string

returned is of vARCHAR2 datatype and is in the

same character set as char1. This function is

equivalent to the concatenation operator (||).

INITCAP     This function is used to covert the beginning

letter of every word in a phrase to uppercase.

INSTR CHARINDEX LOCATE This function search string for substring and

returns an integer indicating the position of the

character in-string that is the first character of

this Occurrence.

LENGTH LEN LENGTH This function returns the length of the given

String

LOWER LOWER LOWER/LCASE Convert the whole string into lower case
LPAD -   It adds the specified character to the given

string on LEFT up to the specified length.

LTRIM LTRIM LTRIM It trims the given string on LEFT side by

removing the specified characters.

RPAD -   It adds the specified character to the given

string on RIGHT up to the specified length.

RTRIM RTRIM RTRIM It trims the given string on RIGHT side by

removing the specified characters.

REPLACE REPLACE REPLACE It returns a string with every occurrence of

search string replaced with the replacement

string.

SUBSTR SUBSTRING SUBSTR This function returns a portion of string

beginning at the character portion and

character length specified.

TRANSLAT - - TRANSLATE returns char with all

E occurrences of each character in ‘from string’ replaced by its corresponding

character in ‘to string’.

TRIM LTRIM/RTRIM - It trims both the leading and trailing

characters from a character string.

UPPER UPPER UPPER/UCASE Converts the given string into uppercase.

 

 

Logical set like function

 

Group function

Group function work on a group of data to obtain aggregate values.

 

ORACLE SQL SERVER DB2UDB
AVG AVG  
COUNT COUNT COUNT
MIN MIN MIN
MAX MAX MAX
SUM SUM SUM

 

Data Functions.

 

 

DATE FUNCTIONS

ORACLE SQL SERVER DB2
ADD_MONTHS DATEADD  
CURRENT_TIMESTAMP DATETIME CURRENT_TIMESTAMP
EXTRACT DATEPART  
LAST_DAY -  
MONTHS_BETWEEN DATEDIFF  
NEXT_DAY -  
ROUND -  
TRUNC    

 

ADD_MONTHS

Returns the date added with the respective number of months.

 

CURRENT_TIMESTAMP

Returns current time and date in the session time zone.

 

EXTRACT(datetime)

Returns a value of the specified datetime field from a datetime or interval value expression.

 

LAST_DAY

Returns the last day of the month in the specified date.

 

MONTHS_BETWEEN

Returns the number of months between the two specified dates.

 

NEXT_DAY

Returns the date of the first weekday of the given character(weekday name)

 

 

Examples

 

Description Date Expression
Now SYSDATE
Tomorrow/ next day SYSDATE +1
Seven days from now SYSDATE +7
One hour from now SYSDATE + 1/24
Three hours from now SYSDATE + 3/24
An half hour form now SYSDATE +1/48
10 minutes from now SYSDATE + 10/1400
30 seconds from now SYSDATE + 30/86400
Tomorrow at 12 midnight TRUNC(SYSDATE +1)
Tomorrow at 8 AM TRUNC(SYSDATE +1) + 8/24
Next Monday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE).’MONDAY’) + 12/24
First day of the month at 12 midnight TRUNC(LAST_DAY(SYSDATE) +1)
The next Monday, Wednesday or Friday at 9 a.m TRUNC(LEAST(NEXT_DAY(sysdate,”MONDAY”),

NEXT_DAY(sysdate,”WEDNESDAY”),NEXT_DAY(

sysdate,”FRIDAY”)))+(9/24)

 

 

 

 

 

 

Registration


A password will be e-mailed to you.

Feedback Form

Name (required)

Email (required)

Feedback