The UPPER Function in SQL

The UPPER function is contrary to the LOWER function and returns all characters in uppercase letters as shown in the following statement where each employee’s first name is displayed in uppercase.

Microsoft Access uses UCASE() function.

    Syntax :
UPPER (character)

    SQL Statement :

SELECT    UPPER(first_name)

FROM       employees;

    Output :

Date and Time Functions in SQL

Every DBMS allows you to store date and time values in tables using specific date data types and in specific formats. Each implementation follows its own storage format to save the date and time, for example, Oracle’s default display and input format for any date is DD-MON-YY. Unfortunately, this default storage varies among different implementations and is, therefore, least portable.

Similar to the character functions, date and time functions are also used to manipulate the representation of data. These function are used not only to present date and time data in some desirable format, but are also helpful in comparing date values, and computing intervals between dates.

To start with, here is an example in Oracle which fetches current date from the system, followed by a list of functions that perform the same task for other platforms.

    SQL Statement :

SELECT    SYSDATE

FROM        DUAL;

    Output :

The following table lists respective functions as employed by various DBMSs to get current system date:

DBMS	FUNCTION	SQL STATEMENT	OUTPUT (Format)
MySQL/MariaDB	CURDATE()	SELECT CURDATE();	2014-11-19 (YYYY-MM-DD)
PostgreSQL	CURRENT_DATE	SELECT CURRENT_DATE;	2014-11-19 (YYYY-MM-DD)
Microsoft Access	NOW()	SELECT Now() FROM Products;	11/19/2014 2:28:17 AM
Microsoft SQL Server	GETDATE()	SELECT GETDATE();	2014-11-19 13:10:02.047
SQLite	DATE()	SELECT DATE('now');	2014-11-19 (YYYY-MM-DD)
DB2	CURRENT DATE	SELECT CURRENT DATE FROM sysibm.sysdummy1;	2014-11-19 (YYYY-MM-DD)

The SUBSTR Function in SQL

The SUBSTR function returns specified characters from character value, starting from P, L characters long.  The following statement fetches records of employees who have ‘lex’ (three characters) in their names starting from second position.

NOTE

In Microsoft Access use MID() function. In Microsoft SQL Server, MySQL, and MariaDB you have to use SUBSTRING().

    Syntax :

SUBSTR (character, P,L)

    SQL Statement :

SELECT    first_name, last_name, salary

FROM      employees

WHERE    substr(first_name,2,3)='lex';   

    Output :

The NVL Function in SQL

When you query a table, the null values stored in a column (e.g commission_pct) are shown as (null). Using the NVL function, you can replace these nulls with some meaningful values. According to the following syntax, if expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.  In the following example, you get a list of employees along with their commissions, replacing null values with zero if the employees are not entitled to get commissions.

NOTE

In the presented example we used a number column and replaced all null values with zero. Null values in a character column must be replaced as shown in the following example, where a character value (‘None’) is used to replace all null values in the state province column.

SELECT city,NVL(state_province, 'None') Province FROM locations;

    Syntax :

NVL (expr1, expr2)

    SQL Statement :

SELECT    last_name, NVL(commission_pct, 0) commission

FROM      employees

WHERE    last_name LIKE 'B%'

ORDER    BY last_name;

    Output  :

The LOWER Function in SQL

The LOWER function returns character, with all letters lowercase. The example presented below transforms all data in first name and job id columns to lower case.

  NOTE   Use LCASE() function for the same purpose in Microsoft Access.

NOTE

As mentioned earlier, single row functions can also be used in the WHERE clause. Let’s see an example. Running the first statement below will not yield any result. Change it so that it matches the second statement. Now you’ll get some rows. The LOWER() function in the second statement fetched the result, because all the job ids are stored in uppercase in the employees table, and you forced the query (by using the LOWER function) to first convert all the column values to lower case, match each value with the provided lower case string (pu_clerk), and then return the result.

Select first_name, last_name, job_id from employees where job_id='pu_clerk';

Select first_name, last_name, job_id from employees where lower(job_id)='pu_clerk';

    Syntax :

LOWER (character)

    SQL Statement :

SELECT    LOWER(first_name||' '||last_name) Name, LOWER(job_id) Job

FROM       employees;

    Output  :

The LENGTH Function in SQL

The LENGTH function returns the length of character. Note that it is a character function that returns the answer in numbers, as shown in the following example where it is used to count number of character in the first names of employees.

NOTE

In Microsoft SQL Server and Microsoft Access use DATALENGTH() and LEN() functions, respectively.

    Syntax :

LENGTH (character)

    SQL Statement :

SELECT    first_name, LENGTH(first_name) Length

FROM       employees;

    Output :

The INITCAP Function in SQL

INITCAP returns character, with the first letter of each word in uppercase, all other letters in lowercase. The following example converts the first letter of the job id column to uppercase, and the rest to lower case. Note that the values stored in this table column are all upper case.

    Syntax 

INITCAP (character)

    SQL Statement :

SELECT      INITCAP(job_id)

FROM         employees;

    Output :