如果是字符串中存储的数字数据需要去掉小数中的尾部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 行受影响)
declare @d decimal(18,4); set @d=3.1400; SELECT CAST(CAST(@d AS decimal(18,2)) AS VARCHAR(10)) /*3.14*/
declare @d decimal(18,4); set @d=3.1400; SELECT convert(numeric(18,2),@d
declare @d decimal(18,4); set @d=3.0000; SELECT replace(RTRIM(replace(RTRIM(replace(@d,0,'')),'.',' ')),' ','.')
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 行受影响)
declare @d decimal(18,4);
set @d=3.1400;
SELECT CAST(CAST(@d AS decimal(18,2)) AS VARCHAR(10))
/*3.14*/
set @d=3.1400;
SELECT convert(numeric(18,2),@d
set @d=3.0000;
SELECT replace(RTRIM(replace(RTRIM(replace(@d,0,'')),'.',' ')),' ','.')
declare @d decimal(18,4);
set @d=3.000;
SELECT replace(RTRIM(replace(replace(RTRIM(replace(@d,0,' ')),' ','0'),'.',' ')),' ','.')
谢谢!结贴!