SQL retains the decimal places of a specified number of digits

Source: Internet
Author: User
Today, I suddenly thought of the problem of saving decimal places in SQL. I found some practical reference on the Internet, which is very helpful for beginners. If you need it, I can refer to it.

Today, I suddenly thought of the problem of saving decimal places in SQL. I found some practical reference on the Internet, which is very helpful for beginners. If you need it, I can refer to it.

In SQL, yes ?? Is it four times faster than M? ? Umeric, decimal, etc? Dao Ji Hui test S? Set ?? Bottom? Quantity,

1. cast (@ value as numeric (18, @ decount ))

2. cast (@ value as decimal (18, @ deccount ))

All? Else e.

Valid SQL 4? ?

Declare @ value float, @ morecount int, @ decount int

: Yes? Row 4? ? Offset? /P>

: Is the size larger than the size to be retained? Didi? Trouble? /P>

: What is small to be retained? Didi ?? /P>

LTRIM (STR (CAST (@ value as decimal (20, @ morecount), 18, @ decount ))

Division retains decimal places

Select cast (10 as float)/3 as decimal (10, 2) as print

There are also Ceiling () and Floor () functions.

One is rounded up and the other is rounded down. It's easy to use.


Decimal type-precision, decimal places, and length


Precision is the number of digits in the number. The number of decimal places is the number to the right of the decimal point in the number. For example, the precision of 123.45 is 5, and the number of decimal places is 2.

The default maximum precision of numeric and decimal data types is 38. In earlier versions of SQL Server, the default maximum value is 28.

The length of the numeric data type is the number of bytes occupied by the number. The length of a string or Unicode data type is the number of characters. The length of binary, varbinary, and image data types is the number of bytes. For example, the int data type can have 10 digits, which are stored in 4 bytes and do not accept decimal places. The precision of the int data type is 10, the length is 4, and the number of decimal places is 0.

When two char, varchar, binary, or varbinary expressions are connected in tandem, the result expression length is the sum of the two source expressions, or 8,000 characters, whichever is less.

When two nchar or nvarchar expressions are connected in tandem, the result expression length is the sum of the two source expressions or 4,000 characters, whichever is less of them.

In addition to the decimal type, the precision and decimal places of the numeric data type are fixed. If an arithmetic operator has two expressions of the same type, the result is of the data type, and the precision and decimal places of this type are defined. If the operator has two expressions of different numeric data types, the Data Type priority rule determines the Data Type of the result. The result defines the precision and decimal places for the data type.

The following table defines how the result accuracy and decimal places are calculated when the operation result is of the decimal type. In the following cases, the result is of the decimal type:

The two expressions are of the decimal type.


One expression is of the decimal type, and the other is of the Data Type lower than the decimal priority.
The operand expression is expressed by expression e1 (precision: p1, number of decimal places is s1) and expression e2 (precision: p2, number of decimal places is s2. The precision and decimal places of non-decimal expressions are the precision and decimal places defined for the expression data type.

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.