Oracle Text Functions

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_lengthfrom productorder 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-103','Bottle, Large');

Number:

select substr(item_id,4,3) item_num,item_descfrom 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 above is an introduction to the usage of Oracle Text Functions, hoping to help you learn.