Learn about Oracle Text Functions

Oracle Text Functions allow us to use common functions. The following describes the usage of several Oracle Text Functions for your reference and learning. We hope that you can have a deeper understanding of Oracle Text Functions.

1) UPPER, LOWER, and INITCAP

These three function changes are provided to them in case sensitivity.

Select upper (product_name) from product;

Select lower (product_name) from product;

Select initcap (product_name) from product;
The function INITCAP can organize messy text as follows:

Select initcap ('this TEXT hAd UNpredictABLE case') from dual;
2) LENGTH

Evaluate the length of the data in the database column.

Select product_name, length (product_name) name_length

From product

Order by product_name;
3) SUBSTR

Substring. format:

SUBSTR source string, starting position, substring length );

Create table item_test (item_id char (20), item_desc char (25 ));

Insert into item_test values ('La-101 ', 'can, small ');

Insert into item_test values ('La-102 ', 'bottle, small ');

Insert into item_test values ('La-000000', 'bottle, large ');
Number:

Select substr (item_id, 4, 3) item_num, item_desc

From item_test;
4) INSTR

Determine the position of the substring in the string. The format is as follows:

INSTR (source string, the string to be searched, and the start position)

Select instr ('this is line one', 'line', 1) from dual;
The return value is the position where the substring first appears in the source string. The return value in the preceding example is 9.

Select item_desc, instr (item_desc, ',', 1)

From item_test;
5) LTRIM, RTRIM, and TRIM

Removes spaces on the left of the string, spaces on the right of the string, and spaces on both sides of the string.

Select ltrim ('abc def ') from dual;

The preceding section describes the usage of Oracle Text Functions.

Provides you with an in-depth understanding of Oracle triggers

Syntax for creating an Oracle package

Differences between Oracle procedures and functions

Use of Oracle to_char Functions

Two cases of Java calling Oracle Functions