Problems caused by not specifying the length of the nvarchar type

1. Problem Reproduction

(1) Prepare test data

Create a data table tb_student and insert the test data shown in.

 

Figure 1 data table tb_student

(2) create a query

Declare @ teacher_name nvarcharset @ teacher_name = 'Lee 4' select * From tb_student as s where S. teacher_name = @ teacher_name

(3) execute the query

Nothing is available, but there is clearly qualified data in the data table. What is the problem?

 

2 Problem Analysis and Solution

After reading the documents, we learned that the default length is used when the length of the nvarchar type is not specified. The default length is 1. Therefore, when the variable @ teacher_name in this example is assigned a value of 'Li si, the space allocated for it is enough to save the character 'lil', and the extra Character '4' will be removed. Therefore, the query here becomes: Query all the student information in the student table whose teacher name is "Li. Obviously, the record that meets this condition does not exist. To solve this problem, you only need to specify a length for the @ teacher_name variable of the nvarchar type. Here, we will allocate ten bytes of storage space for the variable. After correction, we will run the query again and get the correct result.

 

Figure 2 query execution results

 

3. Conclusion

The problems discussed in this article are really too trivial, but these small "trivial" problems often make us feel exhausted.

In order to better avoid these "small problems" that should not occur, the best method is to strengthen the basic knowledge and continuously practice and summarize in the work, that's all.

Problems caused by not specifying the length of the nvarchar type