Shulou(Shulou.com)06/01 Report--

This article shows you how to avoid SQL SERVER space pits and similar PostgreSQL pits. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Although the company is vigorously moving to open source databases, the use of traditional databases will still exist for some time. Recently developed friends have reported a strange phenomenon, that is, external characters are used at the end with\ u0001 (in SQL SERVER this special meaning can be understood as char (1)), stored in the nvarchar character type with a space (in fact, it is also the same in char) And this kind of data will report an error because of this extra space in some special rule engine or decision engine, and when you check it, it doesn't have a space.

You can pay attention to the following figure, if you use the traditional function of len () SQL SERVER to look at the variables with and without spaces at the end of nvarchar or varchar, the length is the same, through datalenght to see the difference between the data, but most developers to check the character length, are using SQL SERVER len () and will get an error result.

The main reason for this problem is how SQL SERVER compares characters. SQL SERVER follows the ANSI/ISO SQL-92 specification to compare characters. So that in character processing, SQL thinks that the comparison between a space at the end of a string and no space is equal in most comparisons.

If it's not clear, let's make a more straightforward comparison.

OK said that strings with and without trailing spaces are the same in many cases, but actually different. In addition, students who want to trim can also save their worries, but they are still different.

In turn, when we compare POSTGRESQL, the main reason is that there are 2.

1 as a traditional enterprise, or a financial enterprise, POSTGRESQL will save a lot of manpower and material resources (especially for development) in the conversion from fee to open source database.

2 PG Fire (concise)

There is no such type as NVARCHAR in PG. We use VARCHAR (VARCHAR has similar problems in SQL SERVER) and PG's text type. The test is conducted on PG admin tools, and the test is also carried out by inserting data with and without spaces.

Insert two pieces of data with an id of 2 with spaces

Through the comparison and proof of the above figure, PG can clearly distinguish which value contains spaces in the query. Those are not the two character types of PostgreSQL version 11, and there are no 'pits' like SQL SREVER.

Here, if we use the char type in PG, there will be a similar situation to SQL SERVER, so in the process of using PG, try to use varchar type or text type if possible.

Conclusion the space pit of SQL SERVER is real, if you want to avoid this pit, it is still troublesome to do it at the database level, and the parallel use of SQL SERVER's rtrim function to remove the right space also ends in failure, while POSTGRESQL varchar text naturally shields this problem, which can be solved by the database itself. On the other hand, it also shows that the character field of the PG table, you should try not to select the CHAR type.

The above content is the pit of SQL SERVER blanks "and how to avoid similar pits in PostgreSQL. Have you learned any knowledge or skills? if you want to learn more skills or enrich your knowledge reserve, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.