Tech Honey

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

SUBSTR Function in Oracle SQL – PLSQL

by

The SUBSTR Function in Oracle SQL / PLSQL is used to extract a string from within another string.

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

SELECT SUBSTR(string_1, start_position [,length])
FROM table_name;

  • string_1 is the string from which the substring is to be extracted.
  • start_position is the position in string_1 from where the extraction will start, the first position is always 1.
  • length is optional parameter, it describes the number of characters to be extracted, if omitted, the SUBSTR function returns the string_1 from the start_position provided.

Also,

  • If start_position is 0, then start_position is treated as 1.
  • If start_position is any positive number then SUBSTR function starts counting from the beginning of the string.
  • If start_position is any negative number then SUBSTR function starts counting from the end of the string, counting backwards.
  • If length is passed as a negative number then SUBSTR function returns NULL.

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

SELECT SUBSTR('Tech Honey',1,4)
FROM dual;

Will return ‘Tech’

SELECT SUBSTR('Tech Honey',1,6)
FROM dual;

Will return ‘Tech H’

SELECT SUBSTR('Tech Honey',3,6)
FROM dual;

Will return ‘ch Hon’

SELECT SUBSTR('Tech Honey',0,6)
FROM dual;

Will return ‘Tech H’

SELECT SUBSTR('Tech Honey',-4,6)
FROM dual;

Will return ‘oney’

SELECT SUBSTR('Tech Honey',-4,-6)
FROM dual;

Will return NULL

Oracle/SQL SOUNDEX Function

by

SQL SOUNDEX FunctionOracle SOUNDEX Function is used to get the phonetic (the way it sounds) representation of any string.SQL SOUNDEX Function syntax and examples are mentioned below.

Oracle SOUNDEX Function Syntax

SELECT SOUNDEX(string_1)1
FROM table_name;

In the above SQL SOUNDEX Function Syntax:-

  • string_1 is the string whose phonetic string will be returned.
  • SOUNDEX Function in Oracle SQL / PLSQL will always return the 1st letter of the string as the first letter of the returned string.
  • Only first 5 consonants are used for determining the NUMERIC portion of the SOUNDEX return value, except when the 1st letter of the string_1 is a vowel.
  • SOUNDEX function is case insensitive and hence returns the same SOUNDEX pattern for UPPER and LOWER case strings.

Oracle SOUNDEX Function – Use with SQL SELECT Statememt

Oracle SOUNDEX Function is used with the SQL SELECT Statement.

For example, the SQL SOUNDEX Function query below return the phonetic representation of the string passed.

SELECT SOUNDEX('Tech Honey')
FROM dual;

The above SQL SOUNDEX Function will return ‘T250’.

Oracle SOUNDEX Function – Using Single Word Example

Oracle SOUNDEX Function can return phonetic representation of single word also.

For example, the SQL SOUNDEX Function returns the phonetic representation of the word passed.

SELECT SOUNDEX('Tech')
FROM dual;

The above SQL SOUNDEX Function returns ‘T200’.

Oracle SOUNDEX Function – Use with Table Column Example

Oracle SOUNDEX Function can be used on column of a table.

For example, the SQL SOUNDEX Function query below return the phonetic version of department names from employee table.

SELECT SOUNDEX(department)
FROM employee;

RTRIM Function in Oracle SQL – PLSQL

by

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

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

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

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

Examples:

Using RTRIM Function in Oracle SQL / PLSQL SELECT statement:

SELECT RTRIM('Tech Honey', 'y')
FROM dual;

Will return ‘Tech Hone’

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

Will return ‘Tech’

SELECT RTRIM('Tech Honey ')
FROM dual;

Will return ‘Tech Honey’

SELECT RTRIM('Tech Honey56789', '0123456789')
FROM dual;

Will return ‘Tech Honey’

SELECT RTRIM('Tech Honey11111', '1')
FROM dual;

Will return ‘Tech Honey’

Oracle PL/SQL RPAD Function

by

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

Oracle RPAD Function Syntax

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

In the above PLSQL RPAD Function Syntax:-

  • string_1 is the string which will be padded from the right 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 RPAD function will pad spaces to the right side of string_1.

Oracle RPAD Function – Use with SQL SELECT Statement Example

SQL RPAD Function is used with the SQL SELECT Statement.

For example, the PLSQL RPAD Function query below returns string padded with spaces on the right side.

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

The above PLSQL RPAD Function query returns ‘Tech ‘.

Oracle RPAD Function – Padding with Spaces Example

SQL RPAD Function can pad a string with white spaces on the right side.

For example, the SQL RPAD Function query below returns string padded with spaces on the right side.

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

The above SQL RPAD Function query returns ‘Tech ‘.

Oracle RPAD Function – Padding with Characters Example

SQL RPAD Function can also pad a string with any character.

For example, the SQL RPAD Function query below returns the string padded witg ‘-‘ on the right side.

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

The above SQL RPAD Function query returns ‘Honey- – -‘

Oracle RPAD Function – Using Exact Length of String Example

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

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

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

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

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

Oracle/SQL Replace Function

by

Oracle REPLACE FunctionOracle/SQL REPLACE Function is used to replace a sequence of characters within a string with another set of characters. Oracle REPLACE Function can be used to substitute a string, a single characters or NULL for a character or set of characters(string). Syntax and examples of SQL REPLACE STRING and SQL REPLACE TEXT are mentioned below.

Oracle/SQL REPLACE Function Syntax

SELECT REPLACE(string_1,string_to_replace[,replacement_string])
FROM table_name;

In the above Oracle REPLACE Function Syntax:-

  • string_1 is the string in which replacements will be made
  • string_to_replace is the string which will be replaced in string_1
  • replacement_string is an optional parameter, if present, then all the occurrences of string_to_replace in string_1will be replaced by replacement_string, if omitted, then all the occurrences of string_to_replace in string_1 will be omitted and the remaining part of string_1 will be returned.

Oracle/SQL REPLACE Function – Replacing with NULL Example

Oracle REPLACE Function is used with the SQL SELECT Statement.

Oracle REPLACE Function can take 2 or 3 arguments.

The first one is the string in which the replacements are to be done.

The second one is the string which is to be replaced.

The third (optional) one is the string which will replace the second string.

For example, the below Oracle REPLACE Function query returns replaces null at places where value is ‘T’.

SELECT REPLACE('Tech Honey', 'T') "PLSQL Replace Function"
FROM dual;

The above Oracle REPLACE Function query returns ‘ech Honey’.

Note: We have aliased REPLACE(‘Tech Honey’, ‘T’) as PLSQL Replace Function.

Oracle/SQL REPLACE Text Example

Oracle/SQL REPLACE TEXT queries can be written to replace a character in string with another string or character.

For example, the SQL REPLACE TEXT query below replaces ‘T’ with ‘t’.

SELECT REPLACE('Tech Honey', 'T','t') "SQL REPLACE TEXT"
FROM dual;

The above REPLACE SQL TEXT query returns ‘tech Honey’

Note:We have aliased SELECT REPLACE(‘Tech Honey’, ‘T’,’t’) as SQL REPLACE TEXT.

Oracle/SQL REPLACE String Example

SQL REPLACE String queries can be written to replace a string with another string or character.

For example, the SQL REPLACE String query below replaces ‘Tech’ with ‘t’.

SELECT REPLACE('Tech Honey', 'Tech','t') "SQL REPLACE String"
FROM dual;

The above REPLACE SQL STRING query returns ‘t Honey’
Note: We have aliased SELECT REPLACE(‘Tech Honey’, ‘Tech’,’t’) as SQL REPLACE String.