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.

                  

TOP in T-SQL

Using the TOP clause lets you select n rows from the beginning of the selected output. 

You can use TOP with the following syntax in T-SQL :

        SELECT TOP n

        *

        FROM table_name;

TOP is mostly used to fetch the TOP n rows according to any particular ORDER . For example selecting the TOP employees taking the maximum salary in an Organization. The query would look like this:

        SELECT TOP 5

        *

        FROM emp

        ORDER BY salary;

We can use the TOP to get random outputs also without using ORDER BY according to our requirements. We may use TOP to select randomly TOP 5 lucky draw winner.

TOP can be used with two other options PERCENT and WITH TIES. These are optional and can be skipped while using the TOP. We will look into each in detail one by one:

• PERCENT : This option is used if we want to use the TOP to select a certain percentage of all the data in the table, such as getting TOP 25% of the entries. The syntax for using TOP with PERCENT is :

        SELECT TOP 25 PERCENT

        *

        FROM emp;

The above query will select TOP 25% of the rows from a table.

• WITH TIES : This option can only be used with ORDER BY. WITH TIES is used to encourage the DUPLICATE values for the last row in the result. When used with WITH TIES option, TOP will return TOP n rows and if the last row is 5^th then if the 6^th row has the same value as the 5^th row then using the WITH TIES option the 6^th row will also be returned . The syntax for using WITH TIES with TOP is :

        SELECT TOP 25 PERCENT

        *

        FROM emp;

emp is the table containing the following data :

Empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-02-04	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-29	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-01-05	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-07-13	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-08-09	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-07-13	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

The output of this query will be:

Empno	ename	Job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-02-04	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-29	1250.00	1400.00	30

Now WITH TIES won’t make any difference in our case. The result seems similar to the query without using the WITH TIES. But if we had 2 entry in our table as :

7654

MARTIN

SALESMAN

7698

1981-09-29

1250.00

1400.00

3

7654

MARTIN

SALESMAN

7698

1981-09-29

1250.00

1400.00

30

Then using TOP without WITH TIES will not return both the rows because on of the above duplicate rows will not be returned as only one comes in TOP 5 and the other one is at 6^th position so only one is returned.

But using TOP with WITH TIES will return BOTH 5^th and 6^th row.

Empno	ename	Job	mgr	Hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-02-04	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-29	1250.00	1400.00	30
7654	MARTIN	SALESMAN	7698	1981-09-29	1250.00	1400.00	30

NOTE : the duplicate values are only displayed id they are the last rows in our output. This will not work with any row appearing at the position less than our n (TOP n) because for any row at position less than `n` the duplicate values are returned even without using WITH TIES and the duplicates are counted in the no. of rows returned.

TOP is executed at last while executing a query because the user expects the final output to be shortened according to the number n , not the random output. For example when using the TOP clause with ORDER BY statement , you will expect it to result the TOP n rows of the ORDERED LIST not the TOP n rows of the random data in the TABLE and then ORDER it. Here is an example to make you understand better:

Consider the following query which selects the TOP 5 salary takers in a company

       SELECT TOP 5

       *

       FROM emp;

       ORDER BY sal;

Our query will return the following rows (TOP 5 salary takers):

Empno	ename	job	mgr	hiredate	sal	comm	deptno
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7788	SCOTT	ANALYST	7566	1987-07-13	3000.00	NULL	20
7566	JONES	MANAGER	7839	1981-02-04	2975.00	NULL	20
7698	BLAKE	MANAGER	7839	1981-01-05	2850.00	NULL	30

But imagine if the TOP clause were to run before the ORDER BY clause what would have been the output:

First the Database will SELECT the TOP 5 rows FROM the table which stores data randomly without any ORDER.

Then the database would execute the ORDER BY clause on the TOP 5 rows selected from the randomly ordered data. The output will look like this:

Empno	ename	job	mgr	hiredate	sal	comm	deptno
7566	JONES	MANAGER	7839	1981-02-04	2975.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
1001	Varun	NULL	NULL	NULL	100.00	NULL	NULL

So if the TOP clause would have been executes before the ORDER BY clause we would get unexpected results.