Common PL/SQL Functions

Common Character Functions

Function	Description
Lower (char)	Converts all uppercase letters in the string expression Char to lowercase letters.
Upper (char)	Converts all lowercase letters in the string expression Char to uppercase letters.
Initcap (char)	Converts the initial letter to uppercase.
Substr (char, start, length)	Returns the length starting from start in the string expression char.
Length (char)	Returns the length of a string expression char.
Ltrim (char)	Removes spaces after the char string expression.
ASCII (char)	Returns the ASCII value of char.
Char (number)	Take the ASCII value of number
Replace (char, str1, str2)	Replace all str1 strings with str2
Instr (char1, char2, start, times)	Search for the char2 string in the char1 string. Start indicates the start position of the search operation, and times indicates the number of searches.

 

Common date functions

Function	Description
Sysdate	Returns the current date and time of the system.
Next_day (day, char)	Returns the first date after the specified date of day and meets the specified char condition. The Char condition can only be the day of the week.
Last_day)	Returns the date corresponding to the last day of the month specified by day.
Add_month (day, n)	Returns the date of day after N months (N is a positive number) or before (N is a negative number ).
Month_between (day1, day2)	Returns the month from which the day1 and day2 dates differ.
Round (day [, FMT])	Round the date data day in the format specified by FMT. By default, the data is rounded to the day.
Trunc (day, [, FMT])	Rounds the day of the date data in the format specified by FMT. By default, the day is truncated.

 

Data Type Conversion Function

Function	Description
To_char	Converts a number or date to a string.
To_number	Convert numeric data into numeric data
To_date	Convert structured data to date data
Convert	Converts a string from one character set to another.
Chartorowid	Converts a string to the rowid data type.
Rowidtochar	Converts a rowid data type to a string.
Nexttoraw	Converts a hexadecimal string to a raw data type.
Rawtohex	Converts a raw binary data type to a hexadecimal string.
To_multi_byte	Converts a single-byte string to a multi-byte string.
To_single_byte	Converts a multi-byte string to a single-byte string.

 

 

 

Set Functions

Function	Description
AVG	Calculate the average value of a column
Count	Count the number of values in a column
Max	Returns the maximum value of a column.
Min	Evaluate the minimum value in a column
Sum	Calculate the sum of values in a column
Stddev	Calculate the standard deviation of a column Value
Variance	Calculate the variance of a column Value