Tech Honey

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

function

Oracle PL/SQL INSTR/InString Function

by

Oracle INSTRING FunctionOracle INSTRING Function is used to get the location of a sub-string in any string. SQL INSTRING Function is also used to get the Nth appearance of a sub-string within a string. PLSQL INSTR Function syntax and elaborate examples are mentioned below.

Oracle INSTR Function Syntax

SELECT INSTR(string1, string2 [, start_position][, nth_appearance])
FROM table_name;

In the above Oracle INSTRING Function Syntax,

  • string1 is the string to be searched
  • string2 is the substring to be searched in string1
  • start_position is the position in the string1 from where the string2 will be searched, it’s an optional parameter, if omitted, the default value is 1, hence search for string2 in string1 will start from 1st alphabet.
  • Nth_appearance is the nth appearance of string2, this is an optional parameter, if omitted the default value is 1.

If string2 is not found in string1 the Oracle SQL INSTRING Function returns 0.

Oracle INSTR FUNCTION – Simple Example

A simple example of SQL INSTRING Function can be to get the location of an alphabet/character in a string.

For example, the SQL INSTR Function query below returns the 1st location of the alphabet ‘e’ in ‘tech honey’.

SELECT INSTR('tech honey','e') "PLSQL INSTR"
FROM dual;

Above SQL INSTRING example returns ‘2’ as ‘e’ appears at the 2nd position for the 1st time.

Note: We have aliased INSTR(‘tech honey’,’e’) as PLSQL INSTR.

Oracle INSTR FUNCTION – Nth Appearance Example

Oracle INSTRING Function can also return the Nth appearance of a character in a string.

For example, the SQL INSTR Function query below returns the 2nd appearance of ‘e’ in ‘tech honey’.

SELECT INSTR('Tech Honey','e',1,2) "PLSQL INSTR"
FROM dual;

Above SQL INSTRING example return ‘9’ as ‘e’ appears at the 9th position for the 2nd time.

Note: We have aliased INSTR(‘Tech Honey’,’e’,1,2) as PLSQL INSTR.

Oracle INSTR FUNCTION – Location of String Example

SQL INSTR Function can also return the location of appearance of a string within a string.

For example, the SQL INSTR Function query below returns the location of appearance of ‘ech’ in ‘Tech Honey is a technical website’.

SELECT INSTR('Tech Honey is a technical website','ech') "PLSQL INSTR"
FROM dual;

Above SQL INSTR example return ‘2’ as ‘ech’ appears at the 2nd position for the 1st time.

Note: We have aliased INSTR(‘Tech Honey is a technical website’,’ech’) as PLSQL INSTR.

Oracle INSTR FUNCTION – Location Of Nth Appearance of String Example

SQL INSTR Function can also return the location of Nth appearance of a string within a string.

For example, the SQL INSTR Function query below returns the location of 2nd appearance of ‘ech’ in ‘Tech Honey is a technical website’.

SELECT INSTR('Tech Honey is a technical website','ech') "Oracle INSTRING"
FROM dual;

Above SQL INSTR example return ‘18’ as ‘ech’ appears at the 18th position for the 2nd time.

Note: We have aliased INSTR(‘Tech Honey is a technical website’,’ech’) as Oracle INSTRING

Oracle PL/SQL INITCAP Function

by

Oracle PLSQL INITCAP FunctionOracle INITCAP Function is used to convert the first alphabet of each word in a string to upper case and the rest of the letters in each word of the string to lower case.SQL INITCAP Function can be used on a table column to get values in INITCAP format.

Oracle INITCAP Function Syntax

SELECT INITCAP(string)
FROM table_name;

In the above Oracle INITCAP Function Syntax:-

string is the string whose first alphabet of each word will be converted to upper case and rest of the letters of each word will be in lower case .

Oracle INITCAP Function – Using SQL SELECT Statement Example

Oracle INITCAP Function is used with the SQL SELECT statement.

For example, the SQL INITCAP Function query below returns the INITCAP version of the string passed.

SELECT INITCAP('tech honey')
FROM dual;

The above return SQL INITCAP Function query returns ‘Tech Honey’.

Oracle INITCAP Function – Converting UPPER CASE to INITCAP

Oracle INITCAP Function can convert an upper case word to INITCAP.

For example, the SQL INITCAP Function query below returns the INITCAP version of the string passed.

SELECT INITCAP('TECH HONEY')
FROM dual;

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

Oracle INITCAP Function – Use with table column

Oracle INITCAP Function can also be applied to a column or field of a database table.

For example, the SQL INITCAP Function query below returns the name of employees in INITCAP format from employee table.

SELECT INITCAP(employee_name)
FROM employee;

DUMP Function in Oracle SQL – PLSQL

by

The DUMP Function in Oracle SQL / PLSQL is used to get a VARCHAR2 data type which tells us the data type code, the length of the expression in bytes and the representation of the expression internally.

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

SELECT DUMP(expression, [return_format],[start_position],[length])
FROM dual;

  • expression is the value to be analyzed.
  • return_format is an optional value which determines the format of the return value. It can have the values as shown in the table below:
Value Description
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
  • start_position and length are also optional parameters which determines the portion of the internal representation to show. If omitted, the dump function will display the decimal notation of the entire internal representation.

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

SELECT
DUMP('Tech Honey')
FROM dual;

Will return ‘Typ=96 Len=10: 84,101,99,104,32,72,111,110,101,121’

SELECT
DUMP('The Internet')
FROM dual;

Will return ‘ Typ=96 Len=12: 84,104,101,32,73,110,116,101,114,110,101,116’

Oracle DECOMPOSE Function

by

Oracle DECOMPOSE FunctionOracle DECOMPOSE Function is be used to convert a string to a UNICODE string. Syntax and examples of SQL DECOMPOSE Function are listed below.

Oracle DECOMPOSE Function Syntax

SELECT DECOMPOSE (string_1)
 FROM table_name;

In the above syntax the “string_1” is the string to be decomposed

Oracle DECOMPOSE Function – Use in SQL SELECT Statement Examples

Oracle/DECOMPOSE Function is used with the SQL SELECT Statement.

For example, the SQL query below returns a UNICODE string of the string passed.

SELECT DECOMPOSE('Tèch Honèy')
FROM dual;

The above SQL query returns ‘Tèch Honèy’.

Oracle DECOMPOSE Function – Use with Single Word Example

The below SQL query returns the UNICODE String of the string passed.

SELECT DECOMPOSE('olé')
FROM dual;

The above SQL query returns ‘ole´’

Oracle/SQL CONVERT Function

by

Oracle CONVERT FunctionSQL CONVERT Function is used to convert a string from one character set to another character set. Oracle CONVERT Function syntax and examples are listed below.

SQL CONVERT Function Syntax

SELECT CONVERT(string, character_set_to, [character_set_from])
FROM table_name;

In the above Oracle CONVERT Function Syntax:-

  • ‘string’ is the string to be converted
  • ‘character_set_to’ is the character set to which the string is to be converted
  • ‘character_set_from’ is the character set from which the string is to be converted

A character set for Oracle CONVERT Function can be any one of the following:

Character Set Explanation
US7ASCII US 7-bit ASCII character set
WE8DEC West European 8-bit character set
WE8HP HP West European Laserjet 8-bit character set
F7DEC DEC French 7-bit character set
WE8EBCDIC500 IBM West European EBCDIC Code Page 500
WE8PC850 IBM PC Code Page 850
WE8ISO8859P1 ISO 8859-1 West European 8-bit character set
US7ASCII US 7-bit ASCII character set

SQL CONVERT Function – Converting to US7ASCII from WE8EBCDIC500 Example

Oracle CONVERT Function is used with the SQL SELECT Statement for conversion.

For example, the Oracle CONVERT Function query below returns the value converted in ‘US7ASCII’ character set.

SELECT CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8EBCDIC500')
FROM dual;

Will return ‘?a?a?a?a?D?-?o?N?Q’

SQL CONVERT Function – Converting to US7ASCII from WE8HP Example

the Oracle CONVERT Function query below returns the value converted in ‘US7ASCII’ character set.

SELECT CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8HP')
FROM dual;

Will return ‘A B C D E a o e i A’