• LOWER is a single row character function.
• It converts character values to lower case.
• The return value has the same data type as the argument CHAR type (CHAR or VARCHAR2).
• Can take a number type as input but LOWER has no effect. Return type is VARCHAR2.

Syntax: LOWER(Character String)

SELECT
    'the code man' Name,
    LOWER('the code man') Lower_Name
  FROM DUAL;

NAME	     LOWER_NAME
------------ ------------
the code man the code man

SELECT
    'The Code Man' Name,
    LOWER('The Code Man') Lower_Name
  FROM DUAL;

NAME	     LOWER_NAME
------------ ------------
The Code Man the code man

SELECT
    'THe CoDE maN' Name,
    LOWER('THe CoDE maN') Lower_Name
  FROM DUAL;

NAME	     LOWER_NAME
------------ ------------
THe CoDE maN the code man

SELECT
    'THE CODE MAN' Name,
    LOWER('THE CODE MAN') Lower_Name
  FROM DUAL;

NAME	     LOWER_NAME
------------ ------------
THE CODE MAN the code man

UPPER and LOWER function can work with number data type as well, though that does not makes sense with numeric data. Oracle does implicit conversion of NUMBER datatype to VARCHAR2 and apply lower/upper function. The return type is VARCHAR2.

SELECT
    123456.909045 Num,
    UPPER(123456.909045) Upper_Num,
    LOWER(123456.909045) lower_Num
  FROM DUAL;

       NUM UPPER_NUM	 LOWER_NUM
---------- ------------- -------------
123456.909 123456.909045 123456.909045

Applying upper/lower to a number datatype seems meaningless, but these 2 functions can help you identify a given string is a number or not.

Q : Write a query to identify a given string is a number or a varchar?

Ans:

SELECT
    CASE
      WHEN lower('1234980') = upper('1234980') THEN 'TRUE'
      ELSE 'FALSE' END Is_Num,
    CASE
      WHEN lower('ABC123XYZ') = upper('ABC123XYZ') THEN 'FALSE'
      ELSE 'TRUE' END Is_Varchar
  FROM DUAL;

IS_N IS_VA
---- -----
TRUE TRUE