我需要删除小数点后的前导零 [英] I need to remove leading zeros after a decimal point

问题描述

这是我第一次在这里发帖，我是一个基本的 SQL 用户，需要帮助.

this is my first time posting here and I am a basic SQL user and need help.

我有一个 varchar 列，用于存储如下数据:

I have a varchar column that stores data like below:

Year.Docid
2007.000000001
2007.000000002
2007.000000003
2007.000000004
2007.000000005
2007.000000006

我需要将此数据连接到另一个小数点后没有所有零的表，有人可以告诉我如何让数据看起来像下面这样:

I need to join this data to another table that does not have all the zeros after the decimal, can someone please show me how to get the data to look like below:

Year Docid
2007.1
2007.2
2007.3
2007.4
2007.5
2007.6

我使用的是微软 SQL SERVER 2012

I am using MICROSOFT SQL SERVER 2012

推荐答案

如果格式是固定的，即YYYY.NNNNNNNNN，就可以得到最后9个字符，转换成int，将结果转换回 varchar 并连接回前 5 个字符:

If the format is fixed, i.e. YYYY.NNNNNNNNN, you could just get the last 9 characters, convert them to int, convert the result back to varchar and concatenate back to the first 5 characters:

LEFT([Year.Docid], 5) + CAST(CAST(RIGHT([Year.Docid], 9) AS int) AS varchar(10))

然而，将 Year 和 Docid 作为两个单独的 int 列存储在两个表中会更有意义.为输出组装它们比每次和连接结果都要容易得多.

However, it would make more sense to store Year and Docid as two separate int columns, in both tables. It is much easier to assemble them just for the output than do this processing every time and join on the results of it.

这篇关于我需要删除小数点后的前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！