Shulou(Shulou.com)05/31 Report--

This article shows you how to use LIKE to use variable types in SQL Server, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

As follows:

CREATE TABLE TEST (ID INT IDENTITY (1), NAME VARCHAR (32)) INSERT INTO dbo.test SELECT 'abc32'; INSERT INTO dbo.test SELECT' abd32'; INSERT INTO dbo.test SELECT 'abe32'; DECLARE @ name VARCHAR (32); SET @ name='ab%'; SELECT * FROM TEST WHERE NAME LIKE @ name; DECLARE @ name1 CHAR (32); SET @ name1='ab%'; SELECT * FROM dbo.TEST WHERE NAME LIKE @ name1

As shown in the screenshot above, when the variable uses the VARCHAR type and the CHAR type, the output is completely different. If you don't have a thorough understanding of the SQL SERVER data type, you may be really confused about this problem. But for people who know more about SQL Server data types, this is really a simple problem that can't be simpler.

As shown below, we add two sentences of SQL in the SQL statement, and use the DATALENGTH function to return the number of bytes of any expression, you will find that the variable of type VARCHAR returns the number of bytes of 3, but the number of bytes of variables of type CHAR is 32, in fact, the reason is that the type of CHAR is of fixed length, that is, when the character you enter is less than the number you specify, for example, char (32), when the length of the character you enter is less than 32, it will fill in the blank value later. When you enter a character length greater than the specified value, it will intercept the excess characters. So the logical meanings of the following two LIKE are different. The logic of LIKE 'ab%' and LIKE' abc%'is completely different.

In fact, you want to confirm from the side is also very simple, the following script comparison shows, carefully understand, maybe you want to understand!

DECLARE @ name CHAR (32); SET @ name='ab%'; SELECT * FROM TEST WHERE NAME LIKE @ name; DECLARE @ name1 CHAR (3); SET @ name1='ab%'; SELECT * FROM dbo.TEST WHERE NAME LIKE @ name1

The above is how to use LIKE to use variable types in SQL Server. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to 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.