DECLARE @T TABLE(TEST FLOAT) INSERT @T SELECT 1.553700 INSERT @T SELECT 0 INSERT @T SELECT 2.03 SELECT CASE WHEN TEST-CAST(TEST AS INT)!=0 THEN LEN(TEST-CAST(TEST AS INT))-2 ELSE 0 END FROM @T
DECLARE @STR AS VARCHAR(50) SELECT @STR='1.553700' SELECT LEN(STUFF(LTRIM(CAST(@STR AS FLOAT)),1,CHARINDEX('.',LTRIM(CAST(@STR AS FLOAT))),'')) --4
用这个 DECLARE @STR AS VARCHAR(50) SELECT @STR='22' SELECT CASE WHEN LTRIM(CAST(@STR AS FLOAT)) LIKE '%.%' THEN LEN(STUFF(LTRIM(CAST(@STR AS FLOAT)),1,CHARINDEX('.',LTRIM(CAST(@STR AS FLOAT))),'')) ELSE 0 END --0
DECLARE @a TABLE(a VARCHAR(20)) INSERT @a SELECT 0 UNION ALL SELECT 1.0 UNION ALL SELECT 1.3234300 UNION ALL SELECT 32. UNION ALL SELECT 0.00 UNION ALL SELECT 3.03230SELECT x, len(stuff(x,1,charindex('.',x+'.'),'')) FROM (SELECT a,cast(CAST(a AS FLOAT) AS VARCHAR(20)) x FROM @a )aa --result /*x -------------------- ----------- 0 0 1 0 1.32343 5 32 0 0 0 3.0323 4(所影响的行数为 6 行)*/
INSERT @T SELECT 1.553700
INSERT @T SELECT 0
INSERT @T SELECT 2.03
SELECT CASE WHEN TEST-CAST(TEST AS INT)!=0 THEN LEN(TEST-CAST(TEST AS INT))-2
ELSE 0 END
FROM @T
SELECT @STR='1.553700'
SELECT LEN(STUFF(LTRIM(CAST(@STR AS FLOAT)),1,CHARINDEX('.',LTRIM(CAST(@STR AS FLOAT))),''))
--4
DECLARE @STR AS VARCHAR(50)
SELECT @STR='22'
SELECT CASE WHEN LTRIM(CAST(@STR AS FLOAT)) LIKE '%.%' THEN
LEN(STUFF(LTRIM(CAST(@STR AS FLOAT)),1,CHARINDEX('.',LTRIM(CAST(@STR AS FLOAT))),''))
ELSE 0 END
--0
right(replace([columnname],'0','')
,len(replace([columnname],'0',''))-charindex('.',replace([columnname],'0','')))
)
INSERT @a SELECT 0
UNION ALL SELECT 1.0
UNION ALL SELECT 1.3234300
UNION ALL SELECT 32.
UNION ALL SELECT 0.00
UNION ALL SELECT 3.03230SELECT x,
len(stuff(x,1,charindex('.',x+'.'),''))
FROM
(SELECT a,cast(CAST(a AS FLOAT) AS VARCHAR(20)) x FROM @a )aa
--result
/*x
-------------------- -----------
0 0
1 0
1.32343 5
32 0
0 0
3.0323 4(所影响的行数为 6 行)*/