Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
Tham khảo tài liệu 'oracle pl/sql for dummies phần 7', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 246 Part IV PL SQL Data Manipulations SUBSTR is needed if you want to retrieve part of existing string as shown here v_tx substr string start position number of chars The start position could be either a positive or negative integer. This would start counting the position from the beginning or from the end of the string as shown here SQL declare 2 v1_tx VARCHAR2 5 ABCDE 3 v2_tx VARCHAR2 5 4 begin 5 v2_tx substr v1_tx 2 6 DBMS_OUTPUT.put_line v2_tx 7 v2_tx substr v1_tx -2 8 DBMS_OUTPUT.put_line v2_tx 9 end 10 BCDE DE PL SQL procedure successfully completed. SQL As shown in these examples you can omit the third parameter requested number of characters . In that case Oracle returns everything from the point you specified to the end of the string. If your starting point is more than the total number of characters in the string Oracle returns NULL. The number of characters requested from the string might not always be the length of the resulting string. It could be less because you might request more characters than the string has. In that case Oracle just returns everything up to the end of the string as shown in Listing 10-18. Listing 10-18 Using SUBSTR SQL declare 2 v1 tx VARCHAR2 5 ABCDE 3 v2 tx VARCHAR2 5 4 begin 5 v2_tx substr v1_tx 2 2 5 6 DBMS_OUTPUT.put_line v2_tx 7 v2_tx substr v1_tx 2 7 7 8 DBMS OUTPUT.put line v2 tx 9 end 10 BC 11 BCDE 12 PL SQL procedure successfully completed. SQL Chapter 10 Basic Datatypes 247 Additional information about Listing 10-18 is shown here 5 11 The code works perfectly because you requested two characters and two characters were returned. 7 12 This line requested 7 characters and 4 were returned because only 5 characters were in the original string. The function INSTR allows you to locate one string character in the other one. You can declare it as shown here v_nr instr string substring position occurrence At the simplest level INSTR returns the number of characters in the original string where the desired substring starts. But
