SQL: Use char and nchar for queries.

Source: Internet
Author: User
Use the SQL Server northwind database's MERs table
1) Code I

1 Declare   @ Ccoustomerid   Char ( 5 )
2 Select   @ Nccoustomerid   =   Null ;
3 Select   *  
4 From MERs
5 Where Customerid Like   Isnull ( @ Nccoustomerid , ' % ' )

 
2) Code 2

1 Declare   @ Ccoustomerid  N Char ( 5 )
2 Select   @ Nccoustomerid   =   Null ;
3 Select   *  
4 From MERs
5 Where Customerid Like   Isnull ( @ Nccoustomerid , ' % ' )


3) Code 3

1 Declare   @ Ccoustomerid VaR Char ( 5 )
2 Select   @ Nccoustomerid   =   Null ;
3 Select   *  
4 From MERs
5 Where Customerid Like   Isnull ( @ Nccoustomerid , ' % ' )


4) code 4 1 Declare   @ Ccoustomerid  NVaR Char ( 5 )
2 Select   @ Nccoustomerid   =   Null ;
3 Select   *  
4 From MERs
5 Where Customerid Like   Isnull ( @ Nccoustomerid , ' % ' )

Four pieces of code are put into the SQL queryserver. Result 1) and 2) the returned message is:
(0 rows affected)

3) and 4) the returned message is:
(91 rows affected)

Cause:
1) and 2) if the variable is null, spaces will be filled after %, and 3) and 4) will not.
Damn it, why does it need to fill in spaces automatically. Really annoying, and rushed to the msdn rummaging, the original:
Char is a fixed-length data type. If the length of the inserted value is smaller than that of the char not null column, spaces are filled on the right of the value until the column length is reached. (Nchar does the same)
Varchar is a variable-length data type. A value smaller than the column length is not filled on the right of the value to reach the column length. (Likewise, nvarchar)
So when the declared variable is Char or nchar, if it is null, it will automatically help you fill in spaces to know the length that meets the definition.
Be careful later ~ Hmm

Now, I don't have to worry about it. I went to the SQL Server online series and found that,ArticleThe name is Char and varchar data. The address is MS-help: // Ms. sqlcc. V9/ms. sqlsvr. v9.zh-CHS/udb9/html/07cc67f6-5057-463b-8975-694a5b4820.6.htm.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.