| Date/Time Functions: |
top
|
|
|
| from_days(N) | Given a daynumber N, returns a DATE value. Not intended for use with values that precede the advent of the Gregorian calendar (1582), due to the days lost when the calendar was changed. - More Info |
| from_unixtime(unix_timestamp) | Returns a representation of the unix_timestamp argument as a value in `YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. - More Info |
| from_unixtime(unix_timestamp,format) | Returns a string representation of the unix_timestamp, formatted according to the format string. Format may contain the same specifiers as those listed in the entry for the DATE_FORMAT( ) function. - More Info |
| period_add(P,N) | Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value. - More Info |
| period_diff(P1,P2) | Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values. - More Info |
| sec_to_time(seconds) | Returns the seconds argument, converted to hours, minutes, and seconds, as a value in `HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. - More Info |
| second(time) | Returns the second for time, in the range 0 to 59. - More Info |
| time_ format(time, format) | This is used like DATE_FORMAT( ), but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. - More Info |
| time_to_sec(time) | Returns the time argument, converted to seconds. - More Info |
| to_days(date) | Given a date, returns a daynumber (the number of days since year 0). - More Info |
| unix_timestamp( ) | If called with no argument, returns a Unix timestamp (seconds since `1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP( ) is called with a date argument, it returns - More Info |
| weekday(date) | Returns the weekday index for date (0 = Monday, 1 = Tuesday, . . . 6 = Sunday). - More Info |
| yearweek(date) | Returns year and week for a date. The second argument works exactly like the second argument to WEEK( ). Note that the year may be different from the year in the date argument for the first and the la - More Info |
| last_day(date) | Returns the date of the last day of the month that contains date. - More Info |
| months_between(date1, date2) | Returns number of months between dates date1 and date2. When date1 is later than date2, the result is positive. If it is earlier, the result is negative. - More Info |
| new_time(date, time_zone1, time_zone2) | Returns the date and time in time_zone2 when date and time in time_zone1 are date. - More Info |
| next_day(date, string) | Returns the date of the first weekday named by string that is later than date. The argument string must be either the full name or the abbreviation of a day of the week in the date language of the session. - More Info |
| sysdate | Returns the current date and time, requiring no arguments. - More Info |
| to_char (date [, format [, `nls_parameter']]) | Converts date to a VARCHAR2 in the format specified by the date format format. When fmt is omitted, date is converted to the default date format. The nls_parameter option offers additional control over formatting options. - More Info |
| to_date(string [, format | Usable only by LONG or LONG RAW expressions, it converts LONG or LONG RAW values in the column long_column to LOB values. It is usable only in the SELECT list of a subquery in an INSERT statement. - More Info |
| current_timestamp | Returns current date and time. - More Info |
| dateadd(datepart, number, date) | Adds a number of dateparts (e.g., days) to a datetime value. - More Info |
| datediff(datepart, startdate, enddate) | Calculates difference between two datetime values expressed in certain dateparts. - More Info |
| datename(datepart, date) | Returns name of a datepart (e.g., month) of a datetime argument. - More Info |
| datepart(datepart, date) | Returns value of a datepart (e.g., hour) of a datetime argument. - More Info |
| isdate(expression) | Validates if a character string can be converted to DATETIME. - More Info |
|
|
|
| String Functions: |
top
|
|
|
| ascii(str) | Returns the ASCII code value of the leftmost character of the string str; returns 0 if str is the empty string; returns NULL if str is NULL. - More Info |
| bin(N) | Returns a string representation of the binary value of N, where N is a long (BIGINT) number. - More Info |
| char(N,...) | Interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped. - More Info |
| export_set(bits,on,off,[separator, [number_of_bits]]) | Returns a string where every bit set in `bit' gets an `on' string and every reset bit gets an `off ' string. Each string is separated with `separator' (default `,') and only `number_of_bits' (default 64) of `bits' is used. - More Info |
| field(str,str1,str2,str3,...) | Returns the index of str in the str1, str2, str3, . . . list. Returns 0 if str is not found. FIELD( ) is the complement of ELT( ). - More Info |
| find_in_set(str,strlist) | Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a `,'. - More Info |
| instr(str,substr) | Returns the position of the first occurrence of substring substr in string str. - More Info |
| lpad(str,len,padstr) | Returns the string str, left-padded with the string padstr until str is 10 characters long. - More Info |
| make_set(bits,str1,str2, . . . ) | Returns a set (a string containing substrings separated by `,' characters) consisting of the strings that have the corresponding bits in bit set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, . . . are not appended to the result. - More Info |
| ord(str) | If the leftmost character of the string str is a multibyte character, returns the code of multibyte character by returning the ASCII code value of the character in the format of: - More Info |
| password(str) | Calculates a password string from the plain-text password str. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table. - More Info |
| reverse(str) | Returns the string str with the order of the characters reversed. - More Info |
| rpad(str,len,padstr) | Returns the string str, right-padded with the string padstr until str is ten characters long. - More Info |
| strcmp(expr1,expr2) | STRCMP( ) returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise. - More Info |
| chartorowid(char) | Converts a value from a character datatype (CHAR or VARCHAR2 datatype) to ROWID datatype. - More Info |
| chr(number [USING NCHAR_CS]) | Returns the character having the binary equivalent to number in either the database character set (if USING NCHAR_CS is not included) or the national character set (if USING NCHAR_CS is included). - More Info |
| convert(char_value, target_char_set, source_char_set) | Converts a character string from one character set to another; returns the char_value in the target_char_set after converting char_value from the source_char_set. - More Info |
| dump(expression [,return_ format [, starting_at [,length]]] ) | Returns a VARCHAR2 value containing a datatype code, length in bytes, and internal representation of expression. The resulting value is returned in the format of return_ format. - More Info |
| hextoraw(string) | Converts string containing hexadecimal digits into a raw value. - More Info |
| initcap(string) | Returns string, with the first letter of each word in uppercase and all other letters in lowercase. - More Info |
| instr(string1, string2, start_at, occurrence) | Searches one character string for another character string. INSRT search char1 with a starting position of start_at (an integer) looking for the numeric occurrence within string2. Returns the position of the character in string1 that is the first character of this occurrence. - More Info |
| length(string) | Returns the integer length of string, or null if string is null. - More Info |
| nls_charset_decl_len(bytecnt, csid) | Returns the declaration width (bytecnt) of an NCHAR column using the character set ID (csid ) of the column. - More Info |
| nls_charset_id(text) | Returns the NLS character set ID number corresponding to text. - More Info |
| nls_charset_name(number) | Returns the VARCHAR2 name for the NLS character set corresponding to the ID number. - More Info |
| nls_initcap(string [,'nlsparameter']) | Returns string with the first letter of each word in uppercase and all other letters in lowercase. The nlsparameter offers special linguistic sorting features. - More Info |
| nls_lower(string, [,'nlsparameter']) | Returns string with all letters lowercase. The nlsparameter offers special linguistic sorting features. - More Info |
| nlssort(string [,'nlsparameter']) | nlssort(string [,'nlsparameter']) Returns the string of bytes used to sort string. The nlsparameter offers special linguistic sorting features. - More Info |
| nls_upper (string[,'nlsparameter']) | Returns string with all letters uppercase. The nlsparameter offers special linguistic sorting features. - More Info |
| rawtohex(raw) | Converts a raw value to a string (character datatype) of its hexadecimal equivalent. - More Info |
| reftohex(expression) | Converts argument expression to a character value containing its hexadecimal equivalent. - More Info |
| rowidtochar(rowid) | Converts a rowid value to VARCHAR2 datatype, 18 characters long. - More Info |
| to_char (number [, format [, `nls_parameter']]) | Converts number to a VARCHAR2 in the format specified by the number format format. When fmt is omitted, number is converted to a string long enough to hold the number. The nls_parameter option offers additional control over formatting options. - More Info |
| to_multi_byte(string) | Returns string with all of its single-byte characters converted to their corresponding multi-byte characters. - More Info |
| to_number(string [, format [,'nls_parameter']]) | Converts a numeric string (of CHAR or VARCHAR2 datatype) to a value of a NUMBER datatype in the format specified by the optional format model format. The nls_parameter option offers additional control over formatting options. - More Info |
| to_single_byte(string) | Returns string with all of its multi-byte characters converted to their corresponding single-byte characters. - More Info |
| vsize(expression) | Returns the number of bytes in the internal representation of expression. When expression is null, it returns null. - More Info |
| char(integer_expression) | Converts a numeric ASCII code to a character. - More Info |
| charindex(expression1, expression2 [, start_location]) | Returns position of the first occurrence of a substring in a string. - More Info |
| datalength(expression) | Returns number of bytes in a character or binary string. - More Info |
| formatmessage (msg_number, param_value [,... n ]) | Constructs a message from an existing message in SYSMESSAGES table (similar to RAISERROR).
- More Info |
| isnumeric(expression) | Validates if a character string can be converted to NUMERIC. - More Info |
| nchar(integer_expression) | Returns the unicode character with the given integer code. - More Info |
|
|
|
| Miscellaneous Functions: |
top
|
|
|
| benchmark(count,expr) | Executes the expression expr count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. - More Info |
| bit_and(expr) | Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT ) precision. - More Info |
| bit_or(expr) | Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT ) precision. - More Info |
| connection_id( ) | Returns the connection ID (thread_id ) for the connection. Every connection has its own unique ID. - More Info |
| elt(N,str1,str2,str3,...) | Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT( ) is the complement of FIELD( ). - More Info |
| get_lock(str,timeout) | Tries to obtain a lock with a name given by the string str, with a timeout of timeout seconds. Returns 1 if the lock is obtained successfully, 0 if the attempt times out, or NULL if an error occurs. - More Info |
| if(expr1,expr2,expr3) | If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), then IF( ) returns expr2, else it returns expr3. IF( ) returns a numeric or string value, depending on the context in which it is used.
- More Info |
| isnull(expr) | If expr is NULL, ISNULL( ) returns 1; otherwise it returns 0. - More Info |
| last_insert_id([expr]) | Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. - More Info |
| load_ file(file_name) | Reads the file and returns the file contents as a string. The file must be on the server, and the user must specify the full pathname to the file and have the file privilege. - More Info |
| release_lock(str) | Releases the lock named by the string str that was obtained with GET_LOCK( ). Returns 1 if the lock is released, 0 if the lock isn't locked by this thread (in which case the lock is not released), and - More Info |
| system_user( ) | These functions return the current MySQL username. - More Info |
| bfilename(`directory','filename') | Returns a BFILE locator associated with a physical LOB binary filename on the server's filesystem in directory. - More Info |
| bfilename(`directory','filename') | Returns a BFILE locator associated with a physical LOB binary filename on the server's filesystem in directory. - More Info |
| dense_rank( ) ( [OVER (query)] ORDER BY...) | Computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the ORDER_BY_clause. - More Info |
| deref(expression) | Returns the object reference of expression, where expression must return a REF to an object. - More Info |
| first_value( expression) over (analytics) | Returns the first value in an ordered set of values. - More Info |
| grouping(expression) | Distinguishes null cause by a super-aggregation in GROUP BY extension from an actual null value. - More Info |
| lag(expression [,offset][,default]) over(analytics) | Provides access to more than one row of a table at the same time without a self join. - More Info |
| last_value(expression) over (analytics) | Returns the last value in an ordered set of values. - More Info |
| lead(expression [,offset][,default]) over(analytics) | Provides access to more than one row of a table at the same time without a self join. Analytic functions are beyond the scope of this text. - More Info |
| make_ref({table_name | view_name} , key [,...n]) | Creates a reference (REF ) to a row of an object view or a row in an object table whose object identifier is primary
key-based.
- More Info |
| ntile(expression) over ( query_partition ORDER BY...) | Divides an ordered data set into a number of buckets numbered 1 to expression and assigns the appropriate bucket number to each row. - More Info |
| numtodsinterval(number, `string') | Converts number to an INTERVAL DAY TO SECOND literal, where number is a number or an expression resolving to a number, such as a numeric datatype column. - More Info |
| numtoyminterval(number, `string') | Converts number to an INTERVAL DAY TO MONTH literal, where number is a number or an expression resolving to a number, such as a numeric datatype column. - More Info |
| nvl(expression1, expression2) | If expression1 is null, expression2 is returned in the place of a null value. Otherwise, expression1 is returned. The expressions may be any datatype. - More Info |
| nvl2(expression1, expression2, expression3) | Similar to NLV, except that if expression1 is not null, expression2 is returned. If expression1 is null, expression3 is returned. The expressions may be any datatype, except LONG. - More Info |
| rank (value_expression) over ( query_partition ORDER BY ...) | Computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_expression in the ORDER_BY_clause. - More Info |
| ref(table_alias) | REF takes a table alias associated with a row from a table or view. A special reference value is returned for the object instance that is bound to the variable or row. - More Info |
| row_number ( ) over ( query_partition ORDER BY ... ) | Assigns a unique number to each row where it is applied in the ordered sequence of rows specified by the ORDER_BY_clause, beginning with 1. - More Info |
| sys_context(`namespace','attribute' [,length]) | Returns the value of attribute associated with the context namespace, usable in both SQL and PL/SQL statements. - More Info |
| sys_guid( ) | Generates and returns a globally unique identifier (RAW value) made up of 16 bytes. - More Info |
| uid | Returns an integer that uniquely identifies the session user who logged on. No parameters are needed. - More Info |
| user | Returns the name of the session user who logged on in VARCHAR2. - More Info |
| userenv(option) | Returns information about the current session in VARCHAR2. - More Info |
| app_name( ) | Returns application name for current session; set by application. - More Info |
| cast(Expression as Data Type) | Converts a valid SQL Server expression to the specified datatype.
- More Info |
| col_length(`table', `column') | Returns column length in bytes. - More Info |
| col_name(table_id, column_id) | Returns column name, given table ID and column ID. - More Info |
| contains({column | }, `contains_search_condition'}) | Searches columns on exact or "fuzzy" matches of contains_seach_criteria. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information. - More Info |
| containstable(table, column, contains_search_condition) | Returns a table with exact and "fuzzy" matches of contains_search_condition. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information. - More Info |
| convert(data_type [(length)], expression [, style]) | Converts data from one datatype to another.
- More Info |
| current_user | Returns username in the current database of the current session. - More Info |
| databasepropertyex(database, property) | Returns database option or property. - More Info |
| db_id(`[database_name]') | Returns database ID and given name. - More Info |
| difference(character_expression, character_expression) | Compares how two arguments sound and returns a number from 0 to 4. Higher result indicates better phonetic match. - More Info |
| file_id(`file_name') | Returns the file ID for the logical filename. - More Info |
| file_name(file_id) | Returns the logical filename for file ID. - More Info |
| filegroup_id (`filegroup_name') | Returns filegroup ID for the logical filegroup name.
- More Info |
| filegroup_name (filegroup_id) | Returns the logical filegroup name for filegroup ID.
- More Info |
| filegroupproperty (filegroup_name, property) | Returns filegroup property value for the specified property.
- More Info |
| fileproperty (file, property) | Returns file property value for the specified property.
- More Info |
| fulltextcatalogproperty(catalog_name, property) | Returns full-text catalog properties.
- More Info |
| fulltextserviceproperty(property) | Returns full-text service level properties. - More Info |
| freetexttable(table { column |*}, `freetext_string' [, top_n_by_rank]) | Used for full-text search; returns a table with columns that match the meaning but don't exactly match value of freetext_string.
- More Info |
| getansinull([`database']) | Returns default nullability setting for new columns. - More Info |
| grouping(column_name) | Returns 1 when the row is added by CUBE or ROLLUP; otherwise, returns 0. - More Info |
| host_id( ) | Returns workstation ID of a given process. - More Info |
| host_name( ) | Returns process hostname. - More Info |
| ident_incr (`table_or_view') | Returns identity-column increment value.
- More Info |
| ident_seed (`table_or_view') | Returns identity seed value.
- More Info |
| identity(data_type [, seed, increment]) As column_name | Used in SELECT INTO statement to insert an identity column into the destination table. - More Info |
| index_col(`table', index_id, key_id) | Returns index column name, given table ID, index ID, and column sequential number in the index key.
- More Info |
| indexproperty(table_id, index, property) | Returns index property (such as Fillfactor). - More Info |
| is_member({`group' | `role'}) | Returns true or false (1 or 0) depending on whether user is a member of NT group or SQL Server role. - More Info |
| is_srvrolemember (`role' [,'login']) | Returns true or false (1 or 0) depending on whether user is a member of specified server role.
- More Info |
| isnull(check_expression, replacement_value) | Returns the first argument if it is not NULL; otherwise, returns the second argument. - More Info |
| newid( ) | Creates a new unique identifier of type uniqueidentifier. - More Info |
| object_id(`object') | Returns object ID and given name. - More Info |
| object_name(object_id) | Returns object name and given ID. - More Info |
| objectproperty (id, property) | Returns properties of objects in the current database.
- More Info |
|
|
|
| Math Functions: |
top
|
|
|
| conv(N,from_base,to_base) | Converts numbers between different number bases; returns a string representation of the number N, converted from base from_base to base to_base ; returns NULL if any argument is NULL. - More Info |
| format(X,D) | Formats the number X to a format like `#,###,###.##', rounded to D decimals. If D is 0, the result has no decimal point or fractional part. - More Info |
| greatest(X,Y,...) | Returns the largest (maximum-valued) argument. - More Info |
| interval(N,N1,N2,N3,...) | Returns 0 if N < N1, 1 if N < N2, and so on. All arguments are treated as integers. It is required that N1 < N2 < N3 < . . . - More Info |
| least(X,Y,...) | With two or more arguments, returns the smallest (minimum-valued) argument. - More Info |
| log(X) | Returns the natural logarithm of X. - More Info |
| md5(string) | Calculates a MD5 checksum for the string. Value is returned as a 32-long hex number. - More Info |
| oct(N) | Returns a string representation of the octal value of N, where N is a long number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL. - More Info |
| truncate(X,D) | Returns the number X, truncated to D decimals. If D is 0, the result has no decimal point or fractional part. - More Info |
| corr(expression1, expression2) over (analytics) | Returns the correlation coefficient of a set of numbered pairs (expressions 1 and 2). It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text). - More Info |
| covar_pop(expression1, expression2) over | Returns the population covariance of a set of number pairs (expressions 1 and 2). It can be used as an aggregate or analytic function - More Info |
| covar_samp(expression1, expression2) over(analytics) | Returns the sample covariance of a set of number pairs (expressions 1 and 2). It can be used as an aggregate or analytic function - More Info |
| cume_dist( ) ( [OVER (query)] ORDER BY...) | The cumulative distribution function computes the relative position of a specified value in a group of values. - More Info |
| percent_rank( ) over ( query_partition ORDER BY...) | Similar to the CUME_DIST analytical function. Rather than return the cumulative distribution, it returns the percentage rank of a row compared to the others in its result set. Refer to the vendor documentation for more assistance. - More Info |
| ratio_to_report(value_exprs) over ( query_partition) | Computes the ratio of a value to the sum of a set of values. If values_expr is null, the ratio-to-report value also is null. - More Info |
| round (number, decimal) | Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to 0 places. Note that decimal, an integer, can be negative to round off digits left of the decimal point. - More Info |
| stdev_pop(expression) over (analytics) | Computes the population standard deviation and returns the square root of the population variance. - More Info |
| var_samp(expression) over (analytics) | Returns the sample variance of a set of numbers after discarding the nulls in the expression number set. Analytic functions are covered in the vendor documentation. - More Info |
| binary_checksum(* | expression [,...n]) | Returns binary checksum for list of expressions or row of a table. - More Info |
| checksum(* | expression [,...n]) | Returns checksum value (computed over row values or expressions provided).
- More Info |
| checksum_agg([ALL | Distinct] expression) | Returns checksum of the values in group. - More Info |
