如何将数字转成字符型,如 12.00 转成 12,23.60 转成23.6,即去除占位的"0",如何实现,谢谢.

[tdcq123]

如何将数字转成字符型,如 12.00 转成 12,23.60 转成23.6,即去除占位的"0",如何实现,谢谢.

[dawugui]

没看懂.

[dawugui]

明白了.
去掉多余的0

如果是字符串中存储的数字数据需要去掉小数中的尾部0， 则参考：


SELECT
	col,
	col_convert = CASE
			WHEN CHARINDEX('.', col) = 0
				THEN col
			WHEN RIGHT(col, PATINDEX('%[^0]%', REVERSE(col))) LIKE '.%'
				THEN LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)))
			ELSE LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)) + 1)
		END
FROM(
	SELECT col = '100' UNION ALL
	SELECT col = NULL UNION ALL
	SELECT col = '.100' UNION ALL
	SELECT col = '.100100' UNION ALL
	SELECT col = '0.' UNION ALL
	SELECT col = '0' UNION ALL
	SELECT col = '100.1010' UNION ALL
	SELECT col = '100.0000' 
)A

-- 结果：
col      col_convert
-------- -----------
100      100
NULL     NULL
.100     .1
.100100  .1001
0.       0
0        0
100.1010 100.101
100.0000 100

(8 行受影响)

[dawugui]

SELECT
    col,
    col_convert = CASE
            WHEN CHARINDEX('.', col) = 0
                THEN col
            WHEN RIGHT(col, PATINDEX('%[^0]%', REVERSE(col))) LIKE '.%'
                THEN LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)))
            ELSE LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)) + 1)
        END
FROM(
    SELECT col = cast(12.00 as varchar) UNION ALL
    SELECT col = cast(23.60 as varchar) 
)A

/*
col                            col_convert
------------------------------ ------------------------------
12.00                          12
23.60                          23.6

(2 行受影响)
*/

[dawugui]

create table tb(col decimal(18,2))
insert into tb values(12.00)
insert into tb values(23.60)
go
SELECT
    col,
    col_convert = CASE
            WHEN CHARINDEX('.', col) = 0
                THEN col
            WHEN RIGHT(col, PATINDEX('%[^0]%', REVERSE(col))) LIKE '.%'
                THEN LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)))
            ELSE LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)) + 1)
        END
FROM (select col = cast(col as varchar) from tb)A

drop table tb

/*
col                            col_convert
------------------------------ ------------------------------
12.00                          12
23.60                          23.6

(2 行受影响)

*/

[-狙击手-]

round()

[tdcq123]

谢谢:潇洒老乌龟
如果记录非常多时,比如几千条,效率是不是很差呀.

[dawugui]

谢谢:潇洒老乌龟
如果记录非常多时,比如几千条,效率是不是很差呀.

几千条算不了什么,几十万的话,可能有点问题.