String Functions in Transact SQL

       

•       ASCII

This Function is used to get the ASCII code of the input char. It returns an int value corresponding to the char. For Example :

              SELECT ASCII('c') AS 'ASCII Code' , 'c' AS 'Character';

OUTPUT:
               ASCII Code  Character
      ----------- ---------
      99          c

•      CHAR

This function is used to get the character corresponding to an integer ASCII code. For Example:

              SELECT CHAR( ASCII('c') ) AS 'Character' , ASCII('c') 'ASCII Code'
OUTPUT:

               Character ASCII Code
      --------- -----------
      c         99

•      CONCAT

As the name suggests this function is used to join together multiple string. For Example:

             SELECT CONCAT('First Name',’ ’,'Last Name');
OUTPUT:

       First Name Last Name

•      LEFT

This functions gives us the specified number of character from the left side of a string. For Example:

             SELECT LEFT('First Name',5);
OUTPUT:

       First

•      RIGHT

This functions gives us the specified number of character from the right side of a string. For Example:

              SELECT RIGHT('First Name',4);
OUTPUT:
       Name

•       LEN

This function is used to get the length of a given String. For Example:

              SELECT LEN('NAME');
OUTPUT:

       4

•      LOWER

This functions converts the given string to all LOWER CASE. For Example:

              SELECT LOWER('First Name');
OUTPUT:

       first name

•       UPPER

This functions converts the given string to all UPPER CASE. For  Example:

               SELECT UPPER('First Name');
OUTPUT:

       FIRST NAME

•       LTRIM

This functions removes all the extra spaces from a string at the LEFTMOST position. For Example:

       SELECT LTRIM('    First Name');
OUTPUT:

       First Name

       NOTE: This function is only concerned with the LEFTMOST spaces, it will not temper   

             with the middle or rightmost spaces.

•       RTRIM

This functions removes all the extra spaces from a string at the RIGHTMOST position. For Example:

       SELECT RTRIM('First Name     ');
OUTPUT:

       First Name

       NOTE: This function is only concerned with the RIGHTMOST spaces, it will not temper   

             with the middle or leftmost spaces.

•       REPLACE

This functions is used to a replace a given pattern in a string to a another pattern. For  Example:

               SELECT REPLACE('First Name','NAME','XXX');
OUTPUT:
       First XXX

'First Name' is the given String.
'NAME' is the pattern to be replaced within the String 'First Name'
'XXX' is the pattern that is added to the the String 'First Name' in place of 'NAME'

       NOTE : If the pattern in not found in the given string then the string is returned as it is with no placement.

                   By default the function searches of the given pattern in the string case-insensitively. To search for the pattern case-sensitively use COLLATION.

•       REVERSE

This functions is used to REVERSE the order of Character in a String. For  Example:

                SELECT REVERSE ('FIRST NAME');
OUTPUT:
       EMAN TSRIF

       
       SELECT REVERSE(REVERSE ('FIRST NAME'))
OUTPUT:

       FIRST NAME

•        STR

This functions is used convert a float expression to String. For  Example:

                SELECT STR(5.09,4,3);

OUTPUT:

        5.09

5.09 is the float expression that we want to convert.
4 is the length of total number of characters displayed in the resulting string. If the resulting string exceeds the given length then the result is trimmed from right to match the given length. 

3 is the length of the number of decimal places to be displayed. In Our case we got only 2 numbers after the decimal because the maximum provided length(4) was exceeded.