Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

TO_MULTI_BYTE Function in Oracle SQL – PLSQL

by

The TO_MULTI_BYTE function in Oracle SQL / PLSQL is used to get a character value with all the single byte characters converted to multi byte characters.

To use TO_MULTI_BYTE function make sure that the data base contains both single and multi byte characters.

The Syntax for the TO_MULTI_BYTE function in Oracle SQL / PLSQL is:

SELECT TO_MULTI_BYTE(character1)
FROM table_name;

  • character1 can be of any type like char, varchar2, nchar or nvarchar2
  • The return type of TO_MULTI_BYTE function will be of the same data type as that of the character1.

Example 1:

SELECT TO_MULTI_BYTE ('Tech Honey')
FROM dual;

Will return the multi byte character value of ‘Tech Honey’

TO_SINGLE_BYTE Function in Oracle SQL – PLSQL

by

The TO_SINGLE_BYTE function in Oracle SQL / PLSQL is used to get a character value with all the multi byte characters converted to single byte characters.

To use TO_SINGLE_BYTE function make sure that the data base contains both single and multi byte characters.

The Syntax for the TO_SINGLE_BYTE function in Oracle SQL / PLSQL is:

SEELECT TO_SINGLE_BYTE(character1)
FROM table_name;

  • character1 can be of any type like char, varchar2, nchar or nvarchar2
  • The return type of TO_SINGLE_BYTE function will be of the same data type as that of the character1.

Example 1:

SELECT TO_SINGLE_BYTE ('Tech Honey')
FROM DUAL;

Will return the single byte character value of ‘Tech Honey’

TO_NUMBER Function in Oracle SQL – PLSQL

by

The TO_NUMBER function in Oracle SQL / PLSQL is used to convert a string to a number data type

The Syntax for the TO_NUMBER function in Oracle SQL / PLSQL is:

SELECT TO_NUMBER(string1, [FORMAT_MASK], [NLS_LANGUAGE])
FROM table_name;

  • string1 is the string that has to be converted to number
  • FORMAT_MASK is optional; it is used to convert the string1 to number.
  • NLS_LANGUAGE is also optional and is used to convert the string1 to number.

Example 1:

SELECT TO_NUMBER('123.456',999.999)
FROM DUAL;

Will return “123.456”

Example 2:

SELECT TO_NUMBER('123.4',999.9)
FROM DUAL;

Will return “123.4”

Example 3:

SELECT TO_NUMBER('123',999.9)
FROM DUAL;

Will return “123”

LEAST Function in Oracle SQL – PLSQL

by

The LEAST function in Oracle SQL / PLSQL is used to get the least or smallest value out of the expressions provided
The Syntax for the LEAST function in Oracle SQL / PLSQL is:

SELECT LEAST(expression1, expression2, expression3. . . ., expressionN)
FROM table_name;
Expression1, expression2, expression3 … expressionN are expressions evaluated by the least function.

  • If the data types of the expressions are different then all the expressions are converted to expression1 data type.
  • In character based comparison one character is considered lower or smaller than another if it has a lower character set.
  • If an expression is NULL in the LEAST function then NULL will be returned as least value.

Example 1:

SELECT LEAST(3,6,12,3)
FROM DUAL;

Will return “3”

Example 2:

SELECT LEAST('3','6','12','3')
FROM DUAL;

Will return “12”

Example 3:

SELECT LEAST('apples', 'grapes', 'bananas')
FROM DUAL;

Will return “apples”

GREATEST Function in Oracle SQL – PLSQL

by

The GREATEST function in Oracle SQL / PLSQL is used to get the greatest value out of the expressions provided.

The Syntax for the GREATEST function in Oracle SQL / PLSQL is:

SELECT GREATEST(expression1, expression2, expression3. . . ., expressionN)
FROM table_name;
Expression1, expression2, expression3 … expressionN are expressions evaluated by the greatest function.

  • If the data types of the expressions are different then all the expressions are converted to expression1 data type.
  • In character based comparison one character is considered greater than another if it has a higher character set.

Example 1:

SELECT GREATEST(3,6,12,3)
FROM DUAL;

Will return “12”

Example 2:

SELECT GREATEST('3','6','12','3')
FROM DUAL;

Will return “6”

Example 3:

SELECT GREATEST('apples', 'grapes', 'bananas')
FROM DUAL;

Will return “grapes”