Educational

Category: Function Reference

  •  Datatype Functions

    The Data Type functions in SQL are a scalar functions that returns information about various data type values. Following are the SQL date type functions −

    Sr.No.Function & Description
    1DATELENGTH()Returns the number of bytes used to represent any expression and also counts the leading and trailing spaces of the expression.
    2IDENT_CURRENT()Returns the last identity value generated for a specified table or view on an identity column.
    3IDENT_INCR()Returns the increment value of the identity column.
    4IDENT_SEED()Returns the original seed value.
    5SQL_VARIANT_PROPERTY()Returns the base data types and other information about a sql_variant value.

  • Conversion Functions

    In SQL, the conversion functions are used to convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype.

    List of Functions

    Following is a list of functions available in the SQL server for Conversion of one datatype to another −

    Sr.No.Function & Description
    1CASTIs used to transform the numerical data into character or string data.
    2CONVERTIs used to transform an expression from one data type to another.
    3PARSEIs used to convert a string data to the desired data format and returns the outcome as an expression.
    4TRY_CASTIs used to return the expression in the chosen data type.
    5TRY_CONVERTIs used to change the datatype of an expression.
    6TRY_PARSEIs used to return a result of an expression that has been converted to the specified data type, or NULL if the conversion is unsuccessful.

  • JSON Functions

    JSON (JavaScript Object Notation) is a simple format for exchanging data. It is self-descriptive, understandable in any language, and irrespective of language. It serves as an XML substitute. JSON is currently a popular data transfer standard.

    The data that returned by modern services is JSON text. SQL Server JSON is the need for data developers to return JSON in SQL. The built-in JSON support in the SQL server is distinct from the native JSON type.

    List of Functions

    Following is a list of scalar functions that returns information about cursors in the SQL server −

    Sr.No.Function & Description
    1ISJSONIs used check a string’s validity for JSON (JavaScript Object Notation) syntax.
    2JSON_OBJECTIs used create JSON objects from the results of evaluating the SQL expressions of the arguments.
    3JSON_ARRAYIt accepts an expression or a comma-separated list of expressions and returns a JSON array containing those values.
    4JSON_VALUEIt accepts a JSON string and returns a scalar value.
    5JSON_QUERYIs used to extract data from a JSON object.
    6JSON_MODIFYIs used to change JSON data kept in a column of a SQL Server table.
    7JSON_PATH_EXISTSIs used to determine whether a particular JSON route is present within a JSON string.

  • Cursor Functions

    In SQL Server, a cursor is a database object that enables us to retrieve and modify data from individual rows one at a time. Nothing more than a row pointer is what a cursor actually is. It always goes together with a SELECT statement. Typically, it consists of a set of SQL statements that iterate through each row one at a time in a set number of rows.

    All Cursor functions are nondeterministic. To put it another way, even with the identical set of input values, these functions do not provide the same outcomes when called.

    List of Functions

    Following is a list of scalar functions that returns information about cursors in the SQL server −

    Sr.No.Function & Description
    1@@CURSOR_ROWSIs used to determine the number of rows in the current cursor.
    2@@FETCH_STATUSIs used to retrieve the most recent opened cursor’s current fetch status.
    3CURSOR_STATUSIs used to determine the current cursor’s status.

  •  Logical Functions

    SQL logical functions are used primarily for checking (or testing) the truth of some conditions. A logical function performs a logical operation or comparison on objects and expressions and returns a boolean value −

    Sr.No.Function & Description
    1CHOSE()Returns a item from the list at the specified index value.
    2IIF()Returns a one of two values, depending on the boolean-expression.
    3NULLIF()Returns NULL if both expressions are equal.

  • Statistical Functions

    SQL server Statistical functions work on SQL server tables and views and exploit all database parallelism, scalability, user privileges and security schemes. Hence the SQL statistical functions can be included and exposed within SQL queries −

    Sr.No.Function & Description
    1@@CONNECTIONS()Returns number of attempted connections.
    2@@CPU_BUSY()Returns amount of time, SQL server spent on an active operation.
    3@@IDLE()returns the total idle time of this SQL server.
    4@@IO_BUSY()Returns the total spent time by this SQL server on performing the input and output operations.
    5@@PACK_RECEIVED()Returns the number of input packets.
    6@@PACK_SENT()Returns the number of output packets.
    7@@PACKET_ERRORS()Returns the number of network packet errors.
    8@@TIMETICKS()Returns the number of microseconds per tick.
    9@@TOTAL_ERRORS()Returns the number of disk errors.
    10@@TOTAL_READ()Returns the number of disk reads.
    11@@TOTOAL_WRITE()Returns the number of disk writes.

  • Text & Image functions

    SQL Text and Image functions operate on text and image data. The text and image functions are −

    Sr.No.Function & Description
    1TEXTPTR()Returns the pointer value of the text or images.
    2TEXTVALID()Returns an integer value(1 for valid, 0 for non-valid).

  • Numeric Functions

    SQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions −

    Sr.No.Function & Description
    1ABS()Returns the absolute value of numeric expression.
    2ACOS()Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1.
    3ASIN()Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1
    4ATAN()Returns the arctangent of numeric expression.
    5ATN2()Returns the arctangent of the two variables passed to it.
    6CEILING()Returns the smallest (closest to negative infinity) integer value that is greater than or equal to this value.
    7COS()Returns the trigonometric cosine of the given value.
    8COT()Returns the trigonometric cotangent of the given value.
    9DEGREES()Returns numeric expression converted from radians to degrees.
    10EXP()Returns the base of the natural logarithm (e) raised to the power of passed numeric expression.
    11FLOOR()Returns the largest integer value that is not greater than passed numeric expression.
    12LOG()Returns the natural logarithm of the passed numeric expression.
    13LOG10()Returns the base-10 logarithm of the passed numeric expression.
    14PI()Returns the value of pi
    15POWER()Returns the value of one expression raised to the power of another expression
    16RADIANS()Returns the value of passed expression converted from degrees to radians.
    17RAND()Returns the random value between 0 and 1.
    18ROUND()Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points
    19SIGN()Returns the sign of a number, indicating whether it is positive, negative, or zero.
    20SIN()Returns the sine of numeric expression given in radians.
    21SQRT()Returns the non-negative square root of numeric expression.
    22TAN()Returns the tangent of numeric expression expressed in radians.

  • Aggregate Functions

    In general, aggregation is a consideration of a collection of objects that are bound together as a single entity. SQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit. Following are the SQL aggregate functions −

    Sr.No.Function & Description
    1APPROX_COUNT_DISTINCT()Returns the approximate number of rows with distinct expression values.
    2AVG()Returns the average of the fields in a particular column that has only numeric values.
    3CHECKSUM_AGG()Returns the checksum value.
    4COUNT_BIG()Returns the count of the number of items or rows selected by the select statement.
    5COUNT()Returns the number of non-NULL values in a particular column.
    6GROUPING()Returns whether the specified column expression in a group by list is aggregate or not.
    7GROUPING_ID()Returns the level of grouping.
    8MAX()Returns the maximum value among the fields in a particular column.
    9MIN()Returns the minimum value among the fields in a particular column.
    10STDEV()Returns the statistical standard deviation of the fields (numerical values) in a particular column.
    11STDEVP()Returns the population standard deviation for the fields (numerical values) in a particular column.
    12SUM()Returns the sum of all the fields (numeric) in a particular column.
    13VAR()Returns the statistical standard variance of the fields (numerical values) in a particular column.
    14VARP()Returns the population standard variance of the fields (numerical values) in a particular column.

  • String Functions

    SQL string functions are used primarily for string manipulation. The following table details the important string functions −

    Sr.No.Function & Description
    1ASCII()Returns numeric value of left-most character
    2CHAR()Returns the character for each integer passed
    3CHARINDEX()Returns the position of a substring within the given string.
    4CONCAT_WS()Returns concatenate with separator
    5CONCAT()Returns concatenated string
    6DIFFERENCE()returns an integer value measuring the difference between the SOUNDEX() values of two different expressions(strings).
    7ESCAPE()Returns a text with escaped characters.
    8FORMAT()Returns the formatted string.
    9LEFT()Returns the extracting string.
    10LEN()Returns the length of the given string.
    11LOWER()Returns returns the lower case of the given string.
    12LTRIM()Returns a string after removing all the white spaces and characters from the string found on the left side.
    13NCHAR()Returns the Unicode character based on the number code.
    14PATINDEX()Returns the position of a pattern in a string.
    15QUOTENAME()Returns a string with a delimiter
    16REPLACE()Returns a new string by replacing all the occurrences of the particular part of a string (substring) with a specified string.
    17REPLICATE()Returns the repeated value.
    18REVERSE()Returns a reversed string.
    19RIGHT()Returns the rightmost characters from the actual(current) string.
    20RTRIM()Returns a string after removing all trailing blanks.
    21SOUNDEX()Returns the Soundex string.
    22SPACE()returns a string consisting of N number of space characters.
    23STR()Returns a number as string.
    24STRING_AGG()Concatenates the values of string expressions and places separator values between them.
    25STRING_SPLIT()Splits a string into rows of substrings.
    26STUFF()Returns a new string by inserting the second expression at the specified deleted place.
    27SUBSTRING()Returns the part of the character.
    28TRANSLATE()Returns a string from the first argument.
    29TRIM()Returns a trimmed string.
    30UNICODE()Returns an integer value of the first character.
    31UPPER()returns a string that has all the characters in upper case.