Sql_substr function Experiment

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/39966251

Format
Intercept function, can be implemented to extract the number of characters specified in the string
substr (String, start_position, [length])
String: Source string
Start_position: Extracted position, the first position in a string is always 1
[Length]: The number of characters extracted, if omitted, SUBSTR will return the entire string

Experimental

Goal:
Experience the use of the SUBSTR function

Experimental ideas:
Implements inserting data from a specified number of characters on a column into a specified column

Simulate on-site requirements:
Assuming a little more complexity, we assume that we need to change the contents of the Ename column to the contents of the Ename1 column, but at this point the field length of the ename1 column exceeds the maximum length of the field requirement for the ename column. After viewing the discovery that the Ename1 column field exceeds the Ename column field length because the ename1 column data type is char (15) and the actual valid data length is less than 7 characters, you only need to extract the first 7 characters of the ename1 column into the ename column. This also indicates that for a fixed-length char type, a space is used to complement the input character if it is not long enough to be fixed.

Description
The emergence of such experimental ideas is actually a reflection of the actual production needs of an example. In the actual production environment, to implement a user's information one column of data extraction to another user, but because of the data type of the two columns, there are spaces resulting in the field length than another user requires the length of the column field, and both cannot change the data type, but the number of valid fields are consistent with the two columns , so think of using substr to do this.

Sql> CREATE TABLE Hyl as SELECT * from EMP where 1=2;--creation Experiment Table createdsql> SELECT * FROM hyl;--Query experiment table, current data is empty empno ename JOB MGR hiredate SAL COMM DEPTNO--------------------------------------------------- -------------sql> ALTER TABLE hyl Add (ename1 char (15));--Add experimental column ename1 to the experimental table for simulating long character fields table alteredsql> insert INTO Hyl (ename1) Select Emp.ename as ename1 from emp;--inserts the ename column data of the EMP table into the Hyl column of the experimental table ename1 in the data type of the fixed-length field, where the ename1 field length is 1514 rows Insertedsql> SELECT * from hyl;--view data inserted into the ename1 column empno ename JOB MGR hiredate SAL COMM DEPTN                                                                        O ENAME1-------------------------------------------------------------------------------                                                                        SMITH                                                                        ALLEN WARDJONES MARTIN                                                                        BLAKE CLARK                                                                        SCOTT                                                                        KING TURNER                                                                        Adams                                                                        JAMES FORD MILLER14 rows Selectedsql> Select COUNT (1) from hyl t where length (T.ENAME1) =15;- -Verify that the ename1 column field has more than 10 rows of data count (1)----------14sql> desc hyl;--View the table structure of the Hyl table, you can see ename1 listed as char (1), ename column varchar2         0) Name TypeNullable Default Comments-------------------------------------------EMPNO Number (4) Y                         Ename VARCHAR2 (Ten) y JOB VARCHAR2 (9) Y MGR number (4) Y      HireDate DATE y SAL number (7,2) y COMM Number (7,2) y DEPTNO number (2) Y ENAME1 CHAR (All) y sql& Gt Update Hyl t set t.ename=t.ename1;--updated ename column ename1 data, the error is as follows, prompting ename field length (10) Less than the Ename1 field length (15), unable to complete the update of the data update Hyl t Set T.ename=t.ename1ora-12899:value too large for column "SCOTT". HYL "." Ename "(actual:15, maximum:10) sql> update hyl t set T.ENAME=SUBSTR (t.ename1,1,7);--Using the SUBSTR function, Update only the contents of the first 7 characters of the ename1 column to the ename column, and then updatedsql> SELECT * from hyl;--again to view the Hyl table and discover that the data for the ENAME column has been successfully updated to the data on the ENAM1 column empno ename JOB MGR hiredate SAL COMM DEPTNO ENAME1-------------------------------------------------------------------------------SMITH                                                              SMITH Allen Allen WARD      WARD Jones Jones                                                             Martin Martin BLAKE                                                             BLAKE Clark Clark SCOTT                                                              SCOTT KING                                                             KING Turner Turner ADAMS ADAMS JAMES JAM            ES FORD                                                  FORD MILLER MILLER14 rows selectedsql> desc hyl;--Again view the data structure of the Hyl table, Learn how to insert the first 7 characters of the ENAME1 column data type char (15) into a ename column with a data type of VARCHAR2 (10) on the name type Nullable Default Comments------------                         -------------------------------EMPNO number (4) y ename VARCHAR2 (Ten) Y         JOB VARCHAR2 (9) Y MGR number (4) Y hiredate DATE                         Y SAL Number (7,2) y COMM number (7,2) y      DEPTNO number (2) y ENAME1 CHAR (y)

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/39966251

Sql_substr function Experiment