1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Using String, Date, Math, Rank and System Functions in SQL Server

Discussion in 'SQL Server' started by MinalS, Sep 29, 2014.

  1. Using String Functions



    User can use the string functions for changing the string values in the result set. They can be used with char or varchar data types. They can be used as a part of the expression.

    The syntax for using the string function is as shown below:

    SELECT function_name (parameters)

    where,

    function_name is the name of the function.

    parameters are the required values for the string function.

    The list of some of the string functions is mentioned below:
    1. ascii ( character_expression): It returns the ASCII code of the character in the expression. Consider the example as SELECT ascii (‘AND’). It returns the value of A as 65.
    2. char ( integer_expression): It returns the character equivalent to the ASCII code value. Consider an example as SELECT char(67). It returns the value as C.
    3. left ( character_expression, integer_expression): It returns the part of the character string equal to the size of the integer_expression characters from the left. Consider the example as SELECT LEFT ( ‘ENGLISH’, 4). It will return the value as ENGL.
    4. len (character_expression): It returns the number of characters in the expression. Consider the example as len( ‘NOVEL’). It returns the value as 5.
    5. replace ( string_expression, string_pattern, string_replacement): It replaces the characters in a string. Consider the example as ( ‘ Pin’, ‘I’, ‘e’). It gives the result as Pen.
    6. reverse ( character_expression): It reverses the string. Consider the example of ‘NOTICE’. It returns the value as ‘ECITON’.
    7. upper( character_expression): It returns the characters in upper case. Consider the example as ‘time’. It results as ‘TIME’
    The example to demonstrate the string function is as shown below:

    Consider the EmpData table containing the following values.

    [​IMG]

    Execute the following query to demonstrate the string function is as shown below:

    [​IMG]

    The output after executing the query is as shown below:

    [​IMG]

    Using Date Functions



    The Date functions in SQL Server are useful for manipulating the date and time values. The user can extract the day, date, time, month and year. The current date of the system can be retrieved by using the getdate() function.

    Some of the date functions are as listed below:
    1. dateadd ( datepart, number, date ): It returns the date by adding the appropriate parameters passed by the user. Consider the example as dateadd (mm,4,’2009-04-03’). It returns the value as ‘2009-08-03’
    2. datepart ( date part, date): It returns the part of the date. Consider the example as datepart ( mm, ‘2010-01-06’). It returns the value as 01.
    3. day ( date ): It returns the value of the day. Consider the example as SELECT day ( ‘2008-01-05’). It returns the value as integer 5.
    4. month ( date ): It returns the value of the month. Consider the example as SELECT month ( ‘2007-07-06’). It returns the value as 07.
    5. year ( date ): It returns the value of the year. Consider the example as SELECT year (‘2002-09-05’). It returns the value as 2002.
    The example to demonstrate the date function is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    Consider the employee table containing the following values.

    [​IMG]

    The query to demonstrate the date function is as shown below:

    [​IMG]

    The result for the query is as shown below:

    [​IMG]

    Using Mathematical Functions



    User can manipulate the numeric values in a result set. The arithmetic and numeric operations can be performed in a result set.

    Some of the mathematical functions are as listed below:
    1. abs ( numeric_expression ): It returns the absolute value of the expression. Consider the example as abs ( -87 ). It returns the value as 87.
    2. acos, asin, atan ( float_expression ): It returns the value in radians for the cosine, sine, or tangent with a floating point value. Consider the example as acos ( -1.0). It returns the value as 3.14159
    3. ceiling ( numeric_expression ): It returns the smallest integer greater than or equal to the value. Consider the example as ceiling ( 16.34 ). It returns the value as 16.
    4. floor ( numeric_expression ): It returns the largest integer less than or equal to the specified value. Consider the example as floor ( 13.34). It returns the value as 13.
    5. pi (): It returns the constant value as 3.1415926.
    6. round () : It returns the numeric expression rounded off to the length specifies in the expression. Consider the example as round ( 14.876 ). It returns the value as 14.870.

    Using Ranking Functions



    The ranking functions are used to generate the sequential numbers for each row based on the rank. The rank() function is used for ranking the values in a table.

    The following functions are used to rank the records in a table.
    1. row_number ()
    2. rank ()
    3. dense_rank ()
    4. ntile ()
    1. row_number() function:: It returns the sequential functions starting from 1, for the rows in the table.

    The example to demonstrate the row_number() function is as shown below:

    Consider the employee table. Execute the following query to demonstrate the function.

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    2. rank() : The rank() function is used for ranking each row in the result set based on the criteria.

    Consider the employee table, execute the following query.

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    3. dense_rank(): The dense_rank() function is used when consecutive ranking is to be applied based on the specific criteria.

    Consider the employee table, execute the following query.

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    4. ntile(): The ntile() function is used to divide the result set into specific number of groups. It accepts a positive integer to distribute the rows into number of groups. It has the number starting from 1.

    Consider the following query to demonstrate the ntile function.

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    Using System Functions



    The system functions are used for querying the system tables. The system tables are a set of tables that are used by the SQL Server to store information about users, databases, tables and security. They are used to access the SQL Server databases and user related information.

    Some of the System functions are as listed below:

    1. host_id: It returns the current host process ID number of the client process. It can be used as SELECT host_id AS ‘HostID’

    The example to demonstrate the host_id is as shown below:

    [​IMG]

    The result for the query is as shown below:

    [​IMG]

    2. datalength: It is used to return the length of the specified parameter.

    The example demonstrating the function is as shown below:

    [​IMG]

    The output for the code is as shown below:

    [​IMG]
     

Share This Page