tailieunhanh - advanced sql Functions in Oracle 10G phần 2

TRIM là một chức năng loại bỏ các ký tự từ đầu bên trái hoặc bên phải của một chuỗi hoặc cả hai đầu. Các chức năng TRIM đã được bổ sung trong Oracle 9. Ban đầu, LTRIM và RTRIM được sử dụng để trang trí các nhân vật từ đầu bên trái hoặc bên phải của chuỗi. TRIM supercedes cả hai. Cú pháp chung của TRIM là: | Common Oracle Functions A Function Review is then the is in This will be replaced along with the word is as shown by the following query SELECT REPLACE This is a test is may be FROM dual This would give REPLACE THISISATEST IS Th may be may be a test If the look for string is not present then the replacing does not occur as shown by the following query SELECT REPLACE This is a test glurg may be FROM dual Which would give REPLACE THISI This is a test The TRIM Function TRIM is a function that removes characters from the left or right ends of a string or both ends. The TRIM function was added in Oracle 9. Originally LTRIM and RTRIM were used for trimming characters from the left or right ends of strings. TRIM supercedes both of these. The general syntax of TRIM is TRIM where trim character FROM subject string The optional where is one of the keywords leading trailing or both. 24 Chapter I If the optional trim character is not present then blanks will be trimmed. Trim character may be any character. The word FROM is necessary only if where or trim character is present. Here is an example SELECT TRIM This string has leading and trailing spaces FROM dual Which gives TRIM THISSTRINGHASLEADINGANDTRAILINGSPACES This string has leading and trailing spaces Both the leading and trailing spaces are deleted. This is probably the most common use of the function. We can be more explicit in the use of the function as shown in the following query SELECT TRIM both from String with blanks FROM dual Which gives TRIM BOTH FROM ST String with blanks In these examples characters rather than spaces are trimmed SELECT TRIM F from Frogs prefer deep water FROM dual Which would give TRIM F FROM FROGSPREF rogs prefer deep water 25 Common Oracle Functions A Function Review Here are some other examples. Example 1 SELECT TRIM leading F from Frogs prefer deep water FROM dual Which would give TRIM LEADING F FROM FR rogs prefer deep water Example 2 SELECT TRIM trailing r from Frogs prefer deep water .