Oracle

Select

String Functions

FunctionDescription
ASCIIGives the ASCII value of the first character of a string.
CHR(n)Character with ASCII value n
CONCATConcatenates two strings
INITCAPChanges the first letter of a string or series of words into uppercase.
INSTRReturns the beginning location of a pattern in a given string.
LENGTHReturns the length of a string.
LOWERConverts every letter of a string to lowercase.
LPADAdds a specified set of characters to the left of the original string.
LTRIMRemoves a set of characters from the left of a string.
REGEXP_COUNTReturns the number of times the pattern is found.
REGEXP_INSTRReturns the location of a pattern in a given string and supports the regular expression.
REGEXP_LIKESearches x for the regular expression defined in the pattern parameter.
REGEXP_REPLACEReplaces string with the regular expression matching supports
REGEXP_SUBSTRReturns the substring with the regular expression matching supports
REPLACEDoes the string replacement
RPADAdds a specified set of characters to the right of the original string.
RTRIMRemoves a set of characters from the right of a string.
SOUNDEXConverts a string to a code value based on similar sounds.
String||StringConcatenates two strings.
SUBSTRRetrieves a portion.
TRANSLATEReplaces a string character by character.
TRIMRemoves a set of characters from both sides.
UPPERConverts every letter to uppercase.
VSIZEReturns the storage size.

Numeric Functions

FunctionDescription
abs(x)absolute value of x
BITAND(x,y)a bitwise AND on x and y.
CEILReturns the ceiling value (next highest integer above a number).
EXPReturns e raised to a value.
FLOORReturns the floor value (next lowest integer below number).
LN(n),LOG(m,n)Natural logarithm, and logarithm base m
ModReturns The Remainder Of N/M Where Both N And M Are Integers.
POWERReturns value raised to some exponential power.
REMAINDER(n1, n2)Identifies the multiple of n2 that is nearest to n1, and returns the difference between those two values.
ROUNDReturns the number rounded to nearest value, adjusts precision.
SignReturns 1 If The Argument Is Positive; -1 If The Argument Is Negative; And 0 If The Argument Is Negative.
Sqrt(x)Square Root Of x
TRUNCReturns the truncated value (removes decimal part of a number, precision adjustable).
SIN(n),
COS(n),
TAN(n)
Sine, cosine, and tangent of n (n expressed in radians)
ASIN(n),
ACOS(n),
ATAN(n)
Arcsine, arccosine, and arctangent of n
SINH(n),
COSH(n),
TANH(n)
Hyperbolic sine, hyperbolic cosine, and hyperbolic tangent of n
ATAN2(x,y)Returns the arctangent of x and y.

Conversion Functions

FunctionDescription
ASCIISTRConverts x to an ASCII string, where x may be a string in any character set.
BIN_TO_NUMConverts a binary number x to a NUMBER.
CAST(x AS type)Converts x to a compatible type specified in type.
CHARTOROWID(x)Converts x to a ROWID.
COMPOSE(x)Converts x to a Unicode string in normalized form.
CONVERT(x,source_char_set,dest_char_set)Converts x from source_char_set to dest_char_set.
DECODEUse it as if then else statement
DECOMPOSE(x)Decomposes the string into the same character set as x and convert x to a Unicode string.
HEXTORAW(x)Converts the character x containing hexadecimal digits (base-16) to a binary number (RAW).
NUMTODSINTERVAL(x)Converts the number x to an INTERVAL DAY TO SECOND.
NUMTOYMINTERVAL(x)Converts the number x to an INTERVAL YEAR TO MONTH.
NVLReturns value if x is null; otherwise x is returned.
NVL2Returns value1 if x is not null; otherwise value2 is returned.
RAWTOHEX(x)Converts the binary number (RAW) x to a VARCHAR2 string in equivalent hexadecimal number.
RAWTONHEX(x)Converts the binary number (RAW) x to an NVARCHAR2 string in the equivalent hexadecimal number.
ROWIDTOCHAR(x)Converts the ROWID x to a VARCHAR2 string.
ROWIDTONCHAR(x)Converts the ROWID x to an NVARCHAR2 string.
TO_BINARY_DOUBLE(x)Converts x to a BINARY_DOUBLE.
TO_BINARY_FLOAT(x)Converts x to a BINARY_FLOAT.
TO_BLOB(x)Converts x to a binary large object (BLOB).
TO_CHARConverts number and date value to string and format
TO_CLOB(x)Converts x to a character large object (CLOB). A CLOB is used to store large amounts of character data.
TO_DATEConverts string to date value
TO_DSINTERVAL(x)Converts the string x to an INTERVAL DAY TO SECOND.
TO_MULTI_BYTE(x)Converts the single-byte characters in x to the corresponding multi-byte characters.
TO_NCHAR(x)Converts x in the database character set to an NVARCHAR2 string.
TO_NCLOB(x)Converts x to a large object NCLOB.
TO_NUMBER(x [, format]Converts x to a NUMBER.
TO_SINGLE_BYTE(x)Converts the multi-byte characters in x to the corresponding single-byte characters.
TO_TIMESTAMP(x)Converts the string x to a TIMESTAMP.
TO_TIMESTAMP_TZ(x)Converts the string xto a TIMESTAMP WITH TIME ZONE.
TO_YMINTERVAL(x)Converts the string x to an INTERVAL YEAR TO MONTH.
TRANSLATE(x,from_string, to_string)Converts all occurrences of from_string in x to to_string.
UNISTR(x)Converts the characters in x to an NCHAR character.

Aggregate Functions

NameDescription
AVGCalculates the average
COUNT(x)Returns the number of rows returned by a query involving x
MAXReturns the maximum value
MEDIANReturns the median value
MINReturns the minimum value
STDDEVReturns the standard deviation
SUMReturns the sum
VARIANCEReturns the variance

Incorrect Usage of Aggregate Functions

Data types

Date functions

FunctionDescription
ADD_MONTHS(d, n)Date d plus n months
LAST_DAY(d)Last day of the month containing date d
MONTHS_BETWEEN(d, e)Months between dates d and e
NEXT_DAY(d, weekday)The first weekday (mon, tue, etc.) after d
NEW_TIME(d, z1, z2)Convert date/time d from time zone z1 to z2
ROUND(d[, fmt])d rounded on fmt (the default for fmt is midnight)
TRUNC(d[, fmt])d truncated on fmt (the default for fmt is midnight)
EXTRACT(c FROM d)Extract date/time component c from expression d
SysdateReturns the current operating system date from database server

Timestamp Functions

FunctionDescription
CURRENT_TIMESTAMPReturns a TIMESTAMP WITH TIME ZONE for the session
EXTRACTExtracts and returns the year, month, day, hour, minute, second, or time zone from x.
FROM_TZ(x, time_zone)Merges xand time_zone into one value.
LOCALTIMESTAMPReturns a TIMESTAMP for the session.
SYSTIMESTAMPReturns a TIMESTAMP WITH TIME ZONE for the database.
SYS_EXTRACT_UTC(x)Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP in UTC.
TO_TIMESTAMP(x, [format])Converts the string x to a TIMESTAMP.
TO_TIMESTAMP_TZ(x, [format])Converts the string x to a TIMESTAMP WITH TIMEZONE.

Time Interval Functions

FunctionDescription
NumToDSIntervalConverts number x to INTERVAL DAY TO SECOND.
NumToYMIntervalConverts number x to INTERVAL YEAR TO MONTH.
TO_DSIntervalConverts string x to INTERVAL DAY TO SECOND.
TO_YMIntervalConverts string x to INTERVAL YEAR TO MONTH.

Time Zone Functions

Introduction

FunctionDescription
CURRENT_DATEreturns the current date in the local time zone in the database session
DBTIMEZONEReturns the time zone for the database
NEW_TIME(x,time_zone1,time_zone2)Converts x from time_zone1 to time_zone2
SESSIONTIMEZONEReturns the database session time zone
TZ_OFFSET(time_zone)Returns the offset for time_zone in hours and minutes
SYS_EXTRACT_UTCExtracts the UTC from a datetime value.

General Functions

FunctionDescription
GREATEST(a, b, ...)Greatest value of the function arguments
LEAST(a, b, ...)Least value of the function arguments
NULLIF(a, b)NULL if a = b; otherwise a
COALESCE(a, b, ...)The first not NULL argument (and NULL if all arguments are NULL)

Analytical Functions

Ranking Functions

Ranking FunctionsDescription
ROWNUMpseudo-column and is calculated as rows are retrieved
ROW_NUMBERrow number for ranking
RANKReturns the rank of items in a group and leaves a gap for a tie.
DENSE_RANKRank the rows and does not skip ties
PERCENT_RANK()Returns the percent rank of a value relative to a group.
CUME_DIST()Returns the position of a value relative to a group.
NTILEGroups data by putting data into a number of percentile groups
HypotheticalRank and Distribution Functions

Group By Functions

Group By FunctionsDescription
PARTITION BYSeparate data groups and perform calculation within that group
CUBEReturns rows containing a subtotal for all combinations of columns
ROLLUPReturns a row containing a subtotal
GROUPINGDeals with NULL value for ROLLUP and CUBE
GROUPING_IDReturns the decimal equivalent of the GROUPING bit vector.
GROUP_IDRemoves duplicate rows returned by a ROLLUP or CUBE.

Inverse Percentile Functions

Inverse Percentile FunctionsDescription
PERCENTILE_DISCChecks the cumulative distribution values
PERCENTILE_CONTChecks the percent rank values in each group

Window Functions

Report Function

Linear Regression Functions

Linear Regression FunctionsDescription
REGR_AVGX(y, x)Returns the average of x after eliminating x and y pairs
REGR_AVGY(y, x)Returns the average of y after eliminating x and y pairs
REGR_COUNT(y, x)Returns the number of non-null number pairs that are used to fit the regression line
REGR_INTERCEPT(y, x)Returns the intercept on the y-axis of the regression line
REGR_R2(y, x)Returns the coefficient of determination of the regression line
REGR_SLOPE(y, x)Returns the slope of the regression line
REGR_SXX(y, x)Returns REG_COUNT (y, x) * VAR_POP(x)
REGR_SXY(y, x)Returns REG_COUNT (y, x) * COVAR_POP(y, x)
REGR_SYY(y, x)Returns REG_COUNT (y, x) * VAR_POP (y)

Subqueries

Table

SQLPlus and Reporting

ItemDescription
ColumnSet up the column format
ScriptExecute a script file
EchoEcho feature
VerifyVerify feature
BreakBreak on a column
CommentAdding remarks to the script
TTITLETop title
BTITLEBottom title

PL/SQL

User, Privilege, Role

Data Dictionaries

  • ALL views have information about all accessible data.
  • USER views have information about your own data.
  • DBA views have database-wide information.
  • V$ views are dynamic performance views.

Appendix

Home » 
Oracle
  1. Select
  2. String Functions
  3. Numeric Functions
  4. Conversion Functions
  5. Aggregate Functions
  6. Data types
  7. Date functions
  8. Timestamp Functions
  9. Time Interval Functions
  10. Time Zone Functions
  11. General Functions
  12. Analytical Functions
  13. Subqueries
  14. Table
  15. SQLPlus and Reporting
  16. PL/SQL
  17. User, Privilege, Role
  18. Data Dictionaries
  19. Appendix