我需要删除小数点后的前导零

本文介绍了我需要删除小数点后的前导零的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我第一次在这里发帖，我是一个基本的 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.