​"/> ​">
search
HomeBackend DevelopmentPHP TutorialShare number formatting examples in SQL
Share number formatting examples in SQLJun 28, 2017 pm 03:41 PM
Whatnumberformat

Use sql statements to format double data, for example, only take out the two digits after the decimal point
. Main method

--Take the number before the decimal place, regardless of rounding
select left('30000.72234', charindex('.', '30000.72234')-1)
Among them: charindex(' .', '30000.72234') to get the position of the decimal point; -1 means everything before the decimal point. If you want to get n after the decimal point, just write +n. It's quite useful.

--Separate every three digits with commas, leave 2 decimal places, and do not consider rounding
select convert(varchar, cast(round(30000.72234,0) as money), 1)

--Separate every three digits with commas, leave no decimal places, consider rounding
select left(convert(varchar, cast(round(30000.72234,0) as money), 1) , charindex('.', convert( varchar, cast(round(30000.72234,0) as money), 1))-1)


二. Comparison of CAST and ROUND
1.
SELECT CAST('123.456' as decimal) will get 123 (the decimal point will be omitted).
If you want to get the two decimal places.
You need to change the above to
SELECT CAST('123.456' as decimal(38, 2))

===>123.46
It will be automatically rounded!

2.
SELECT ROUND(123.75633, 2, 1),
ROUND(123.75633, 2)
The two values ​​obtained by the above SQL are different. The former one is: 123.75000, the latter one is: 123.76000.
Because the former has been truncated before rounding, leaving 2 digits after the decimal point.
The latter is not intercepted, and when rounded, it will naturally get 123.76000


Problem: It is necessary to calculate the ratio of the value queried in the Oracle database. SQL similar to the following:
Select Round((discount/Amount),2) from dual;

The numbers calculated in this way can meet most needs. But for numbers less than 1, a problem arises...data similar to .45 is displayed. The 0 in front of 0.45 is gone.

The following summarizes two methods to solve the problem of missing 0.

The first solution:
SELECT DECODE (TRUNC (1/100),0, REPLACE (1/100, '.', '0.'),TO_CHAR (1/100))
FROM DUAL;

You only need to replace 1/100 with the percentage you need.

The second solution:
SELECT RTrim(To_Char(1/100,'FM99999999990.9999'),'.') FROM dual;

The replacement method is the same as above.

Detailed comparison of the two methods:

The first formula is more complicated, but it can handle general numerical values.

The second one looks simple, but the number of digits in the formatted value must be greater than the number of digits in the calculation. For example, SELECT RTrim(To_Char(200,'FM90.9999'),'.') FROM dual; The displayed value is

##, because it has exceeded the formatting range.

--------------------



I found out that the value of field a in table tb is 0.4286, what should I do? It can be converted to a percentage of 42.9% (keep one decimal place after the decimal point)
For example
select a from tb
0.4286
The result I want to get is
42.9%

SQL code

select cast(cast(a*100 as decimal(18,1)) as varchar) + '%' from tb

#####

The above is the detailed content of Share number formatting examples in SQL. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
光盘格式化是什么光盘格式化是什么Aug 17, 2023 pm 04:02 PM

光盘格式化是指将光盘的文件系统进行重建和清空的过程,在光盘格式化过程中,所有的数据都会被彻底删除,同时文件系统会被重新建立,以便在光盘上重新存储数据。光盘格式化可以用于保护数据安全、修复光盘故障和清除病毒等目的,在进行光盘格式化时,需要备份重要数据、选择适当的文件系统,并耐心等待格式化完成。

为什么d盘无法格式化为什么d盘无法格式化Aug 30, 2023 pm 02:39 PM

d盘无法格式化的原因有该盘正在被其他程序或进程使用、该盘上存在损坏的文件系统、硬盘故障和权限问题的。详细介绍:1、D盘无法格式化可能是因为该盘正在被其他程序或进程使用,在Windows操作系统中,如果有程序正在访问D盘上的文件或文件夹,系统将无法执行格式化操作;2、D盘无法格式化可能是因为该盘上存在损坏的文件系统,文件系统是操作系统用来组织和管理存储设备上的文件和文件夹的等等。

提升Java时间日期格式化解析性能的方法提升Java时间日期格式化解析性能的方法Jul 01, 2023 am 08:07 AM

如何优化Java开发中的时间日期格式化解析性能摘要:在Java开发中,时间日期格式化与解析是常见的操作,但是由于时间日期格式复杂多样且处理的数据量庞大,往往会成为性能瓶颈。本文将介绍几种优化Java开发中时间日期格式化解析性能的方法,包括使用缓存、减少对象创建、选择适当的API等。一、引言时间日期格式化与解析在Java开发中非常常见。然而,在实际应用中,由于

修复:Rufus 无法在 Windows PC 中创建可启动 USB 问题修复:Rufus 无法在 Windows PC 中创建可启动 USB 问题Apr 29, 2023 am 09:19 AM

Rufus是一款出色的工具,可以轻松创建可启动的USB驱动器。这个小巧而时尚的工具的效率令人惊叹,通常可以提供无错误的操作。但是,有时创建一个新的可启动USB记忆棒会弹出一些错误消息,从而在刻录过程中绊倒。如果您在使用Rufus时遇到任何困难,您可以按照以下步骤为您的问题找到快速解决方案。修复1–运行驱动器的错误检查器在使用Rufus重试之前,您可以运行驱动器的错误检查器工具来扫描驱动器是否存在任何错误。1.同时按下Windows键+E键打开文件资源管理器。然后,点击“这台电脑

使用fmt.Sprint函数将多个值格式化为字符串并返回,包括类型信息使用fmt.Sprint函数将多个值格式化为字符串并返回,包括类型信息Jul 25, 2023 am 09:01 AM

使用fmt.Sprint函数将多个值格式化为字符串并返回,包括类型信息在Go语言中,fmt包提供了许多函数用于将数据格式化为字符串。其中,fmt.Sprint函数可以将多个值格式化为字符串并返回。与fmt.Sprintf函数不同的是,fmt.Sprint函数返回一个字符串,而不是一个格式化后的字符串。下面是一个使用fmt.Sprint函数的简单示例代码:pa

使用PHP的json_encode()函数将数组或对象转换为JSON字符串并格式化输出使用PHP的json_encode()函数将数组或对象转换为JSON字符串并格式化输出Nov 03, 2023 pm 03:44 PM

使用PHP的json_encode()函数将数组或对象转换为JSON字符串并格式化输出,可以让数据在不同的平台和语言之间进行传递和交换变得更加容易。本文将介绍json_encode()函数的基本用法,以及如何将JSON字符串格式化输出。一、json_encode()函数的基本用法json_encode()函数的基本语法如下:stringjson_encod

格式化u盘有什么后果格式化u盘有什么后果Jan 13, 2021 pm 05:32 PM

格式化u盘的后果:1、清空U盘,会将u盘中的文件全部删除;2、可以消除U盘上的一些逻辑性的错误和非顽固性病毒或流氓程序;3、如果经常性反复格式化u盘,会降低U盘的使用寿命。

手机格式化后能恢复数据吗手机格式化后能恢复数据吗Jun 30, 2023 pm 02:12 PM

手机格式化之后还能恢复数据,只要对手机的数据进行备份就可以恢复。很多手机都自带数据备份功能,若想要恢复被删除或格式化的数据,可以点开手机自带备份功能,按照页面指示进行备份操作。

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools