Introduction to Oracle Text functions _oracle

Oracle text functions make our common functions, and here are some examples of the use of Oracle text functions for your reference learning, hoping to give you a deeper understanding of Oracle text functions.

(1) UPPER, Lower and Initcap

These three function changes are provided to their stylistic case.

Select Upper (product_name) from product;

Select Lower (product_name) from product;

Select Initcap (product_name) from product;

function Initcap is able to organize cluttered text as follows:

Select Initcap (' This TEXT hAd unpredictable case ') from dual;

(2) LENGTH

Find the length of data in the database column.

Select Product_name,length (product_name) Name_length from product order by

Product_Name;

(3) SUBSTR

To take a substring, in the form:

SUBSTR (source string, starting position, substring length);

CREATE TABLE Item_test (item_id char (), Item_desc char);

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_desc from

item_test;

(4) INSTR

Determines the position of the substring in the string, in the following format:

INSTR (source string, string to find, find starting position)

Select InStr (' This are line one ', ' line ', 1] from dual;

The return value is where the substring first appears in the source string from the start position. The return value for the example above is 9.

Select Item_desc, InStr (Item_desc, ', ', ', ', 1) from Item_test;

(5) LTRIM, RTrim and Trim

Remove the space to the left of the string, remove the space to the right of the string, and remove the space on both sides of the string.

Select LTrim (' abc def ') from dual;

The above is the Oracle text function usage Introduction, hoped that is helpful to everybody's study.