lopezatienza.com

Funciones numéricas

ABS Returns the absolute value of the expression.
ACOS Returns the angle in radians for the given cosine.
ASIN Returns the angle in radians for the given sine.
ATAN Returns the angle in radians for the given tangent.
ATN2 Returns the angle in radians whose tangent is in between two given floats.
CEILING Returns the smallest integer greater than or equal to a given expression.
COS Returns the cosine from a given angle (in radians).
COT Returns the cotangent from a given angle (in radians).
DEGREES Returns a degrees value from a given expression (in radians).
EXP Returns the exponential value of the expression.
FLOOR Returns the largest integer that is less than or equal to the given expression.
LOG Returns the natural logarithm of a given expression.
LOG10 Returns the base 10 logarithm of the given expression.
PI Returns number PI (3.1415926535897931...).
POWER Returns the result of an expression in power n.
RADIANS Returns radians from degrees.
RAND Returns the random float number in the range of 0 to 1.
ROUND Returns a numeric expression rounded to a specified length or precision.
SIGN Returns +1 for positive expressions, 0 for zero, and –1 for negative expressions.
SIN Returns the sine from a given angle (in radians).
SQUARE Returns the expression squared.
SQRT Returns the square root of the given expression.
TAN Returns the tangent of the given expression.

Funciones de cadenas

CHR Returns the character having the binary equivalent to n.
CONCAT Returns char1 concatenated with char2.
INITCAP Returns char, the first letter in uppercase, all other letters in lowercase.
LOWER Returns char, all letters in lowercase.
LPAD Returns char1, left-padded to length n with the sequence of characters in char2; if char1 is longer than n, this function returns the portion of char1 that fits in n.
LTRIM Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank.
NLS_INITCAP Returns char, with the first letter of each word in uppercase, all other letters in lowercase.
NLS_LOWER Returns char, with all letters lowercase.
NLSSORT Returns the string of bytes used to sort char.
NLS_UPPER Returns char, with all letters uppercase.
REPLACE Returns char with every occurrence of search_string replaced with replacement_string.
RPAD Returns char1, right-padded to length n with char2 replicated as many times as necessary.
RTRIM Returns char, with all rightmost characters that appear in set removed.
SOUNDEX Returns a character string containing the phonetic representation of char.
SUBSTR Returns a portion of string, beginning at a specified character position that is substring_length characters long. SUBSTR calculates lengths using characters defined by the input character set.
SUBSTRB Same as STRING, except SUBSTRB uses bytes instead of characters.
SUBSTRC Same as STRING, except SUBSTRC uses Unicode-complete characters.
SUBSTR2 Same as STRING, except SUBSTR2 uses UCS2 codepoints.
SUBSTR4 Same as STRING, except SUBSTR4 uses UCS4 codepoints.
TRANSLATE Returns char with all occurrences of each character in from_string, replaced by its corresponding character in to_string.
TREAT Returns a declared type of expression.
UPPER Returns char, with all letters uppercase. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same data type as char.

Funciones de fecha

DATEADD Returns a new datetime value based on the passed value plus a specified interval.
DATEDIFF Returns number of time units (seconds, days, years, etc.) passed between two dates.
DATENAME Returns character string representing a specified date part of the date.
DATEPART Returns an integer representing the specified date part.
DAY Returns an integer representing the day part of a date.
GETDATE Returns the current system’s date and time.
GETUTCDATE Returns date/time value for the current UTC time.
MONTH Returns an integer representing the month part of a date.
YEAR Returns an integer representing the year part of a date.

Funciones de conversiónes de tipos

ASCIISTR Returns an ASCII string in the database character set. The value returned contains only characters that appear in SQL, plus the forward slash (/).
BIN_TO_NUM Converts a bit vector to its equivalent number. Each argument in this function represents a bit in the bit vector. Each expression must evaluate to 0 or 1. This function returns NUMBER.
CAST Converts one built-in data type or collection-type value into another built-in data type or collection-type value.
CHARTOROWID Converts a value from the CHAR, VARCHAR2, NCHAR, or
NVARCHAR2 data type to the ROWID data type.
COMPOSE Returns a Unicode string in its fully normalized form in the same character set as the input.
CONVERT Converts a character string from one character set to another. The data type of the returned value is VARCHAR2.
DECOMPOSE Returns a Unicode string after canonical decomposition in the same character set as the input.
HEXTORAW Converts char containing hexadecimal digits in the CHAR, VARCHAR2, NVARCHAR, or NVARCHAR2 character set to a raw value.
NUMTODSINTERVAL Converts n to an INTERVAL DAY TO SECOND LITERAL. n can be a number or an expression resolving to a number.
NUMTOYMINTERVAL Converts number n to an INTERVAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number.
RAWTOHEX Converts raw data type to a character value containing its hexadecimal equivalent.
RAWTONHEX Converts raw data type to an NVARCHAR2 character value containing its hexadecimal equivalent.
ROWIDTOCHAR Converts a rowid value to a VARCHAR2 data type. The result of this conversion is always 18 characters long.
ROWIDTONCHAR Converts a rowid value to a NVARCHAR2 data type. The result of this conversion is always 18 characters long.
TO_CHAR Converts the NCHAR, NVARCHAR2, CLOB, or NCLOB data type to the database character set TO_CHAR Converts date of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH A LOCAL TIME ZONE data type to a value of the VARCHAR2 data type in the format specified by the date format.
TO_CHAR Converts n of the NUMBER data type to a value of the VARCHAR2 data type.
TO_CLOB Converts NCLOB values in an LOB column or other character string to CLOB values.
TO_DATE Converts char of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2, data type to a value of the DATE data type.
TO_DSINTERVAL Converts a character string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND TYPE.
TO_LOB Converts LONG or LONG RAW values in the column long_column to LOB values.
TO_MULTI_BYTE Returns char with all of its single-byte characters converted to their corresponding multibyte characters. The value returned is in the same data type as char.
TO_NCHAR Converts a character string, CLOB, NCLOB, from the database character set to the national character set.
TO_NCHAR Converts a character string of the DATE,TIMESTAMP, TIMESTAMP WITH TIME ZONE ......etc. data type from the database character set to the national character set.
TO_NCHAR Converts a number to a string in the NVARCHAR2 character set.
TO_NCLOB Converts CLOB values in a LOB column or other character string to NCLOB values.
TO_NUMBER Converts char to a value of the NUMBER data type.
TO_SINGLE_BYTE Returns char with all of its multibyte characters converted to their corresponding single-byte characters.
TO_YMINTERVAL Converts a character string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH data type, where char is the character string to be
converted.
TRANSLATE ... USING Converts text into the character set specified for conversions between the database character set and the national character set.
UNISTR Takes as its argument a string in any character set and returns it in Unicode in the database Unicode character set.

Funciones de agregado

AVG Returns the average of all the group values; NULLs are ignored.
BINARY_CHECKSUM Returns the binary checksum value computed over a row of a table or a list of expressions.
CHECKSUM Returns the checksum value computed over a row of a table or a list of expressions.
CHECKSUM_AGG Returns the checksum of values in a group.
COUNT Returns the number of selected rows or input values.
COUNT_BIG Returns the number of selected rows or input values.
GROUPING Causes an additional column to be output with a value 1 or 0.
MIN Returns the lowest input value.
MAX Returns the greatest input value.
STDEV Returns the statistical standard deviation for values in the expression.
STDEVP Returns the statistical standard deviation for the population of the values in the expression.
SUM Returns the sum of the input values.
VAR Returns the statistical variance for the values in a given expression.
VARP Returns the statistical variance for a population of s in the values in a given expression.

Funciones de texto e imagenes

PATINDEX Returns the starting position of a first occurrence of the specified pattern within an expression.
TEXTPTR Returns the text pointer value that corresponds to a text, ntext, or image in varbinary format.
TEXTVALID Returns the results from checking

Funciones de cursores

@@CURSOR_ROWS Returns the number of rows in the last opened cursor.
@@FETCH_STATUS Returns the status of the last FETCH statement from any cursor within the current session.
CURSOR_STATUS Is a scalar function that shows whether the procedure has

Funciones de Metadata

COL_LENGTH Returns the defined length of a column.
COL_NAME Returns the column name from the column ID.
COLUMNPROPERTY Returns information about a column or a parameter in a procedure.
DATABASEPROPERTY Returns the property value of a given database.
DATABASEPROPERTYEX Returns the current setting for the specified property.
DB_ID Returns database identification number.
DB_NAME Returns the current database name.
FILE_ID Returns the file identification number for a given logical file.
FILE_NAME Returns the file name from a given identification number.
FILEGROUP_ID Returns the file identification number for a given filegroup.
FILEGROUP_NAME Returns the filegroup name from a given identification number.
FILEGROUPPROPERTY Returns a specified filegroup property value for a given filegroup.
FILEPROPERTY Returns a specified file property value for a given file.
FULLTEXTCATALOGPROPERTY Returns information about full-text catalog.
FULLTEXTSERVICEPROPERTY Returns information about full-text service level properties.
INDEX_COL Returns indexed column name.
INDEXKEY_PROPERTY Returns information about the index key.
INDEXPROPERTY Returns the property value from a given index name and table ID.
OBJECT_ID Returns database object’s ID given name.
OBJECT_NAME Returns database object’s name by ID.
OBJECTPROPERTY Returns information about objects in the current database.
@@PROCID Returns stored procedure identifier for the current procedure.
TYPEPROPERTY Returns information about data types.
SQL_VARIANT_PROPERTY Returns information about the base data type in addition to other property information.
FN_LISTEXTENDEDPROPERTY Returns extended property values of the database objects.

Funciones de Configuración

@@CONNECTION Returns the number of opened or attempted connections.
@@DATEFIRST Returns the value of the SET DATEFIRST parameter.
@@DBTS Returns the current value of the datestamp data type.
@@LANGUAGE Returns the name of the language for the current session/database.
@@LANGID Returns ID of the language for the current session/database.
@@LOCK_TIMEOUT Returns lock timeout in seconds.
@@MAX_CONNECTION Returns the maximum number of simultaneous user connections.
@@MAX_PRECISION Returns the precision level used by numeric data types.
@@NESTLEVEL Returns the nesting level of the current stored procedure.
@@OPTIONS Returns bitmask information about current SET options.
@@REMSERVER Returns name of the remote server.
@@SPID Returns the number (ID) of the current process/session.
@@SERVERNAME Returns the name of the local server.
@@SERVICENAME Returns the name of the registry key under which the SQL Server instance is running.
@@TEXTSIZE Returns the current value of the TEXTSIZE option.
@@VERSION Returns the date, version, and processor

Funciones de Seguridad

IS_MEMBER Indicates whether the user is a member of a Windows NT group or an SQL Server role.
IS_SRVROLEMEMBER Indicates whether current login is a member of the specified role.
SUSER_ID Returns login ID for the current user.
SUSER_NAME Returns login name for the current user.
SUSER_SID Returns user’s security identification number (SID) from login name.
SUSER_SNAME Returns user’s login name from security identification number (SID).
USER_ID Returns user’s database identification number from user name.
USER_NAME Returns database user’s name from identification number. USER Returns the current user’s database name.
HAS_DBACCESS Indicates whether current user has access to the specified database.
fn_trace_geteventinfo Returns information about events being traced.
fn_trace_getfilterinfo Returns information about filters applied to a specified trace.
fn_trace_getinfo Returns information about traces.
fn_trace_gettable Returns trace information in a table format.

Funciones de Sistema

APP_NAME Returns the application name of the current session (if set).
CASE Evaluates a list of conditions and returns one value.
CAST Explicitly converts one data type into another data type.
COALESCE Returns the first non-Null expression on the list.
CONVERT Explicitly converts one data type into another data type, behaves similar to the CAST function.
CURRENT_TIMESTAMP Returns the current date/time; equivalent of GETDATE().
CURRENT_USER Returns the current user; equivalent of USER_NAME.
DATALENGTH Returns the number of bytes in an expression.
@@ERROR Returns the error number of the last Transact-SQL statement.
FORMATMESSAGE Formats a message from the existing one in the sysmessages table.
GETANSINULL Returns the default NULL ability for the database for the session.
HOST_ID Returns the ID of the computer.
HOST_NAME Returns the name of the host computer.
IDENT_INCR Returns the increment value of any identity column in a table or a view.
IDENT_SEED Returns the identity seed value of any identity column in a table or a view.
@@IDENTITY Returns the last inserted identity value.
IDENTITY Used to insert into an identity column.
ISDATE Determines whether an expression is a valid date type (or could be converted into one).
ISNULL Determines whether the expression is NULL.
ISNUMERIC Determines whether the expression is numeric.
NEWID Returns a unique value for the UNIQUEIDENTIFIER data type.
NULLIF Returns NULL if two expressions are equivalent.
PARSENAME Returns the specified path of the object name.
PERMISSIONS Returns a value for the bitmap specifying permissions for the object for the current user.
@@ROWCOUNT Returns the number of rows affected by the last statement.
ROWCOUNT_BIG Returns the rows affected by the last statement (bigint).
SESSION_USER Returns the user’s name that is to be inserted into a table when no default is specified.
STATS_DATE Returns the date when the index statistics were updated.
SYSTEM_USER Returns the name of the current user with admin privileges (Windows authenticated).
@@TRANCOUNT Returns the number of pending transactions for the current session.
USER_NAME Returns the database user name from a given identification number.
COLLATIONPROPERTY Returns the property of a given collation.
SCOPE_IDENTITY Returns the last identity value inserted in the identity column for the current scope.
SERVERPROPERTY Returns the value for the specified server property.
SESSIONPROPERTY Returns the value for the specified session property.
fn_helpcollation Returns a list of all collation supported.
fn_servershareddrives Returns the names of the shared drives that could be used by the clustered server.
fn_virtualservernodes Returns a list of nodes on which a virtual server can run.

Funciones de Estadística

@@CPU_BUSY Returns the time (in milliseconds) since the start of the SQL Server.
@@IDLE Returns idle time (in milliseconds) since the start of the SQL Server.
@@IO_BUSY Returns the time (in milliseconds) since the start of the SQL Server, when it was busy with I/O operations.
@@PACK_RECEIVED Returns the number of input packets received since the start of the SQL Server.
@@PACK_SENT Returns the number of output packets received since the start of the SQL Server.
@@PACKET_ERRORS Returns the number of error packets on the network that have occurred since the start of the SQL Server.
@@TIMETICKS Returns the number of milliseconds per CPU tick.
@@TOTAL_ERRORS Returns the number of disk write/read errors since the start of the SQL Server.
@@TOTAL_READ Returns the number of physical disk reads since the start of the SQL Server.
@@TOTAL_WRITE Returns the number of physical disk writes since the start of the SQL Server.
fn_virtualfilestats Returns I/O statistics for the database files.

Funciones de RowSet

CONTAINSTABLE Returns a table for those columns containing characterbased data types for precise or fuzzy matches of a single word or phrase.
FREETEXTTABLE Returns a table for those columns containing characterbased data types for values that match the meaning but not the exact word.
OPENQUERY Executes a specified pass-through query on a linked OLEDB data source.
OPENROWSET Provides an ad-hoc method of connecting and accessing remote data through OLEDB.
OPENDATASOURCE Provides ad-hoc connection information, as a part of a four-part object name, without using a linked server.
OPENXML Provides a rowset view of the XML document.

Referencias

SQL Bible

Autor: Antonio Lopez Atienza