loading..
Ðóññêèé    English
20:39
go

Float(n)

Once in a social network was asked how to remove trailing zeros in decimal numbers. This was associated with the preparation of a report, which sums concatenate with the text. In the conditions of the problem was stated limit to two decimal places.

Here's an example of data and the desired result:

given    to obtain
0.00    0
10.00    10
2.50    2.5
100.00    100
11.33    11.33

Solutions have been proposed, based on analysis of the line. I also chose the wrong way and proposed the following solution:

Console
Execute
  1. SELECT num, 
  2. CASE WHEN CAST(num AS INT) = num 
  3.            THEN CAST(CAST(num AS INT) AS VARCHAR) 
  4.            WHEN CAST(num*10 AS INT) = num*10 
  5.            THEN LEFT(CAST(num AS VARCHAR), LEN(CAST(num AS VARCHAR)) - 1) 
  6.             WHEN CAST(num*100 AS INT)=num*100 
  7.             THEN CAST(num AS VARCHAR) 
  8. END fnum
  9. FROM( 
  10. SELECT 0.00 AS num 
  11. UNION ALL SELECT 10.00 
  12. UNION ALL SELECT 2.50 
  13. UNION ALL SELECT 100
  14. UNION ALL SELECT 11.33 
  15. ) X

I do not know how much it would still be continued, if one member did not notice that all problems are solved by conversion to the data type   Òèï äàííûõ ñ ïëàâàþùåé òî÷êîé. float. Really:

Console
Execute
  1. SELECT num, CAST(num AS FLOAT) fnum
  2. FROM( 
  3. SELECT 0.00 AS num 
  4. UNION ALL SELECT 10.00 
  5. UNION ALL SELECT 2.50 
  6. UNION ALL SELECT 100
  7. UNION ALL SELECT 11.33 
  8. ) X

However, you need to remember the approximate nature of this type, namely the magnitude of the mantissa in scientific representation of the number.

In accordance with the standard in this type of data specified argument - FLOAT (n), which can take values from 1 to 53. The  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server, an argument value in the range 1 – 24, interprets it as 24, which corresponds to the accuracy of 7 digits, and in the range 25 - 53 as 53, which corresponds to the accuracy of 15 digits. The default is 53.

The following example illustrates the above:

Console
Execute
  1. SELECT num, 
  2. CAST(num AS FLOAT(24)) num_24, 
  3. CAST(num AS FLOAT(53)) num_53
  4. FROM( 
  5. SELECT 1234567.80 AS num 
  6. UNION ALL SELECT  12345678.90
  7. UNION ALL SELECT 123456789012345.60 
  8. UNION ALL SELECT 1234567890123456.70 
  9. ) x

num    num_24    num_53
1234567.80    1234568    1234567.8
12345678.90    1.234568E+07    12345678.9
123456789012345.60    1.234568E+14    123456789012346
1234567890123456.70    1.234568E+15    1.23456789012346E+15

MySQL (version 5.0)

Does not support the conversion to type FLOAT.

PostgreSQL (version 8.3.6)

Almost similar behavior, except that for the parameter in the range 1 - 24 precision is 6 digits. Accordingly, recent results will look like this:

num    num_24    num_53
1234567.80        1.23457e+006    1234567.8
12345678.90    1.23457e+007    12345678.9
123456789012345.60    1.23457e+014    123456789012346
1234567890123456.70    1.23457e+015    1.23456789012346e+015

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.