Tech Honey

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

function

LTRIM Function in Oracle SQL – PLSQL

by

The LTRIM Function in Oracle SQL / PLSQL is used to remove the specified characters in a string from the left side.

Syntax for the using the LTRIM Function in Oracle SQL / PLSQL is:

SELECT LTRIM(string_1,[string_2])
FROM table_name;

  • string_1 is the string which will be trimmed from the left side
  • string_2 is the string which will be matched in string_1 for trimming, it’s an optional parameter, if omitted then LTRIM function will remove all the spaces in string_1from left side.

Examples:
Using LTRIM Function in Oracle SQL / PLSQL SELECT statement:

SELECT LTRIM('Tech Honey', 'T')
FROM dual;

Will return ‘ech Honey’

SELECT LTRIM('Tech Honey', 'Tech')
FROM dual;

Will return ‘Honey’

SELECT LTRIM(' Tech Honey')
FROM dual;

Will return ‘Tech Honey’

SELECT LTRIM('56789Tech Honey', '0123456789')
FROM dual;

Will return ‘Tech Honey’

SELECT LTRIM('111111Tech Honey', '1')
FROM dual;

Will return ‘Tech Honey’

Oracle/PLSQL LPAD Function

by

Oracle PLSQL LPAD FunctionOracle LPAD Function is used to pad a string with a set of characters from the left side. SQL LPAD Function can not only pad a string with spaces but also with characters supplied as arguments. Syntax and examples of PLSQL LPAD Function are mentioned below.

Oracle LPAD Function Syntax

SELECT LPAD(string_1,padded_length[,padding_string])
FROM table_name;

In the above PLSQL LPAD Function Syntax:-

  • string_1 is the string which will be padded from the left side. The string_1 should not be NULL.
  • padded_length is the number of characters in return, if the padded_length is smaller than string_1, then LPAD function will pad spaces to the left side of string_1.

Oracle LPAD Function – Use with SQL SELECT Statement Example

Oracle LPAD Function is used with the SQL SELECT Statement.

For example, the PLSQL LPAD Function query below returns the string padded with spaces.

SELECT LPAD('Tech', 7)
FROM dual;

The above PLSQL LPAD Function returns ‘ Tech’.

Oracle LPAD Function – Simple Example

The simplest example of SQL LPAD Function would be to pad a string.

For example, the SQL LPAD Function query below returns a string after padding from the left side.

SELECT LPAD('Tech', 7)
FROM dual;

The above SQL LPAD Function query returns ‘ Tech’.

Notice that the SQL LPAD Function pads the string with 3 spaces on the left side.

Oracle LPAD Function – Padding with Characters

Oracle LPAD Function can not only pad a string with spaces but can also pad with supplied characters.

For example, the SQL LPAD Function query returns a string after padding with ‘-‘ from the left side.

SELECT LPAD('Honey', 8, '-')
FROM dual;

The above SQL LPAD Function query returns ‘- – -Honey’.

Notice that the length of the returned string becomes 8 characters after padding with ‘-‘.

Oracle LPAD Function – Padding Exact Characters Example

If we try to pad a string with the same length as that of the string, then the Oracle LPAD Function returns the string.

For example, the SQL LPAD Function query below tries to pad a string of length 10.

SELECT LPAD('Tech Honey', 10, '-')
FROM dual;

The above SQL LPAD Function query returns ‘Tech Honey’.

Notice that there is no change in the passed and returned string as there was no scope of padding for SQL LPAD Function.

UPPER Function in Oracle SQL – PLSQL

by

The UPPER Function in Oracle SQL / PLSQL is used to convert the letters of a string to upper case

Syntax for the using the UPPER Function in Oracle SQL / PLSQL is:

SELECT UPPER(string)
FROM table_name;

string is the string whose letters will be converted to UPPER case.

Examples:

Using UPPER Function in Oracle SQL / PLSQL SELECT statement:

SELECT UPPER('Tech Honey')
FROM dual;

Will return ‘TECH HONEY’

SELECT UPPER('tech honey')
FROM dual;

Will return ‘TECH HONEY’

SELECT
UPPER('TECh HOney')
FROM dual;

Will return ‘TECH HONEY’

Oracle/SQL LOWER Function

by

Oracle LOWER FunctionSQL LOWER Function is used to convert the letters/alphabets of a string to lower case. Oracle LOWER Function converts all the letters/alphabets of the passed string to lower case.

SQL LOWER Function Syntax

SELECT LOWER(string)
FROM table_name;

In the above Oracle LOWER Function Syntax:-
string is the string whose letters will be converted to lower case.

SQL LOWER Function – Using SQL SELECT Statement Examples

SQL LOWER Function is used with the SQL SELECT Statement.

For example, the Oracle LOWER Function query returns the letters of words in lower case.

SELECT LOWER('Tech Honey') "Oracle LOWER"
FROM dual;

Will return ‘tech honey’

Note: We have aliased LOWER(‘Tech Honey’) as Oracle LOWER.

SQL LOWER Function – Using Upper Case Word Example

SQL LOWER Function can convert a word from UPPER to LOWER case.

For example, the below Oracle LOWER Function query returns the lower case version of the upper case word passed.

SELECT LOWER('TECH HONEY') "Oracle/SQL LOWER Function"
FROM dual;

Will return ‘tech honey’

Note: We have aliased LOWER(‘TECH HONEY’) as Oracle/SQL LOWER Function.

SQL LOWER Function – Using any format of Word Example

Oracle LOWER Function returns all the alphabets in lower case.

For example, the below Oracle LOWER Function query returns all the letters in lower case irrespective of original case of letters.

SELECT LOWER('TECh HOney')
FROM dual;

Will return ‘tech honey’.

Note: We have aliased LOWER(‘TECh HOney’) as “Oracle/SQL LOWER Function”.

Oracle PL/SQL LENGTH Function

by

Oracle PLSQL Length FunctionOracle PL/SQL LENGTH Function is used to return the length of a string. Oracle LENGTH Function calculates length by considering characters of input.

Oracle PL/SQL LENGTH Function Sytntax

SELECT LENGTH(string)
FROM table_name;

“string” is the string whose length is to be determined, if the length of the string is NULL then LENGTH Function returns NULL.

Oracle PL/SQL LENGTH Function – Use with SELECT Statement Example

The Oracle LENGTH Function is used with the SQL SELECT Statement.

For example the SQL SELECT Statement query with LENGTH Function returns the length of the string passed.

SELECT LENGTH('tech honey')
FROM dual;

Above mentioned SQL LENGTH Function will return ‘10’ as length of the string.

Oracle PL/SQL LENGTH Function – Use with NULL Example

SQL LENGTH Function can accept NULL as a parameter.

For example the SQL query below returns the length of NULL parameter.

SELECT LENGTH(NULL) "Oracle LENGTH Function"
FROM dual;

Above mentioned Oracle LENGTH Function will return ‘NULL’.

Oracle PL/SQL LENGTH Function – Zero Length String Example

Oracle LENGTH Function can also accept a string of 0 length.

For example the query below returns the length of a string with 0 length.

SELECT LENGTH('')
FROM dual;

Above mentioned PL/SQL LENGTH Function will return ‘NULL’.