SQL取小数位数的问题

Question

比如数据库里有如下数据列名Null
#
1
1.001
1.010
1.000我想得到的小数位数如下列名0
0
0
3
2
0
请问如何写效率最高谢谢

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 10:25:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'
--------------开始查询--------------------------
select isnull(charindex('.',col)+1,0) from tb
----------------结果----------------------------
/* -----------
0
1
1
3
3
3(6 行受影响)
*/

Answer 2

--修改
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 10:25:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'
--------------开始查询--------------------------
select case when charindex('.',col)=0 then 0 else isnull(charindex('.',col)+1,0) end from tb
----------------结果----------------------------
/*-----------
0
0
0
3
3
3(6 行受影响)
*/

Answer 3

 
--> 生成测试数据: @tb
DECLARE @tb TABLE (列名 varchar(5))
INSERT INTO @tb
SELECT null UNION ALL
SELECT '#' UNION ALL
SELECT '1' UNION ALL
SELECT '1.001' UNION ALL
SELECT '1.010' UNION ALL
SELECT '1.000' --SQL查询如下:
SELECT 
    CASE WHEN CHARINDEX('.',列名)>0 AND ISNUMERIC(列名)=1 
                  AND CAST(CAST(列名 AS money) AS int)=CAST(列名 AS money) THEN 0
         WHEN CHARINDEX('.',列名)>0 AND ISNUMERIC(列名)=1 THEN
             LEN(列名)-PATINDEX('%[^0]%.%',REVERSE(列名))-1
         ELSE 0 END
FROM @tb

Answer 4

----------------------------------------------------------------
--我是烤鸭，抄袭小F的数据
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 10:25:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'
SELECT 0 FROM TB WHERE CHARINDEX('.',COL)=0
UNION ALL
SELECT 
LEN(SUBSTRING(REVERSE(COL),PATINDEX('%[^0]%',REVERSE(COL))
,CHARINDEX('.',REVERSE(COL))-PATINDEX('%[^0]%',REVERSE(COL))))
FROM TB WHERE CHARINDEX('.',COL)>0
/*
0
0
3
2
0
*/

Answer 5

小改一下
----------------------------------------------------------------
--我是烤鸭，抄袭小F的数据
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 10:25:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'
SELECT COL,0 FROM TB WHERE CHARINDEX('.',COL)=0 OR COL IS NULL
UNION ALL
SELECT COL
,LEN(SUBSTRING(REVERSE(COL),PATINDEX('%[^0]%',REVERSE(COL))
,CHARINDEX('.',REVERSE(COL))-PATINDEX('%[^0]%',REVERSE(COL))))
FROM TB WHERE CHARINDEX('.',COL)>0
/*
NULL 0
[#] 0
1 0
1.001 3
1.010 2
1.000 0
*/

Answer 6

嗯嗯!谢谢！另外
能不能不用union all ？

Answer 7

DECLARE @t TABLE(Num VARCHAR(10))
INSERT @t VALUES(NULL)
INSERT @t VALUES('#')
INSERT @t VALUES('1')
INSERT @t VALUES('1.001')
INSERT @t VALUES('1.010')
INSERT @t VALUES('1.000')SELECT CASE WHEN CHARINDEX('.',isnull(Num,''))=0 OR 
Patindex('%[1-9]%',RIGHT(isnull(Num,''),LEN(isnull(Num,''))-
CHARINDEX('.',isnull(isnull(Num,''),''))))=0 THEN 0
ELSE
LEN(Num)-CHARINDEX('.',isnull(Num,''))+1
-Patindex('%[1-9]%',Reverse(Num))
END
FROM @t
/*
0
0
0
3
2
0
*/

Answer 8

----------------------------------------------------------------
--我是烤鸭，抄袭小F的数据
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 10:25:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'SELECT COL
,CASE WHEN CHARINDEX('.',COL)>0 THEN
LEN(SUBSTRING(REVERSE(COL),PATINDEX('%[^0]%',REVERSE(COL))
,CHARINDEX('.',REVERSE(COL))-PATINDEX('%[^0]%',REVERSE(COL))))
ELSE 0 END
FROM TB 
/*
NULL 0
[#] 0
1 0
1.001 3
1.010 2
1.000 0
*/

Answer 9

DECLARE @t TABLE(Num VARCHAR(10))
INSERT @t VALUES(NULL)
INSERT @t VALUES('#')
INSERT @t VALUES('1')
INSERT @t VALUES('1.001')
INSERT @t VALUES('1.010')
INSERT @t VALUES('1.000')SELECT Num,
LEN=CASE WHEN CHARINDEX('.',isnull(Num,''))=0 OR 
Patindex('%[1-9]%',RIGHT(isnull(Num,''),LEN(isnull(Num,''))-
CHARINDEX('.',isnull(isnull(Num,''),''))))=0 THEN 0
ELSE
LEN(Num)-CHARINDEX('.',isnull(Num,''))+1
-Patindex('%[1-9]%',Reverse(Num))
END
FROM @t
/*
Num        LEN
---------- -----------
NULL       0
#          0
1          0
1.001      3
1.010      2
1.000      0
*/

Answer 10

还是P梁的好 我怎么就没想到money和int数据的相互转化呢 

Answer 11

话说我突然想到一个更简单的办法
----------------------------------------------------------------
--我是烤鸭，抄袭小F的数据
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 10:25:13
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'SELECT COL
,CASE WHEN CHARINDEX('.',COL)>0 AND ISNUMERIC(COL)=1 THEN
LEN(STUFF(CONVERT(VARCHAR(100),CONVERT(FLOAT,COL)),1,CHARINDEX('.',COL),''))
ELSE 0 END
FROM TB 
/*
NULL 0
[#] 0
1 0
1.001 3
1.010 2
1.000 0
*/

Answer 12

谢谢各位
我自己也写了个 大家看看行不行 ？MAX((CASE CHARINDEX('.', a) WHEN 0 then 0 ELSE LEN(RTRIM(replace(a,'0',' ')))-CHARINDEX('.', a)END )) 

Answer 13

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'
 
select * ,0 from tb where isnumeric(col)<>1 
union all 
select * ,case when charindex('.',reverse(cast(col as float)))=0 then 0 else charindex('.',reverse(cast(col as float)))-1 end 
from tb where isnumeric(col)=1/*
----- -----------
NULL  0
[#]   0
1     0
1.001 3
1.010 2
1.000 0
*/

Answer 14

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(5))
insert [tb]
select null union all
select '[#]' union all
select '1' union all
select '1.001' union all
select '1.010' union all
select '1.000'
go
alter function f_getnum(@str varchar(50))
returns int
as
begin
declare @num int
declare @ck varchar(5)
if isnumeric(@str) = 0
set @num = 0
else
begin
if(charindex('.' , @str)= 0)
set @num = 0
else
begin
set @ck = right(@str , 1)
while @ck = '0'
begin
set @str = left(@str , len(@str)-1)
set @ck = right(@str , 1)
end
set @num = len(@str) - charindex('.' , @str)
end
end
return @num
end
go
select col , dbo.f_getnum(col) as num from tb
--------------------------
col   num
----- -----------
NULL  0
[#]   0
1     0
1.001 3
1.010 2
1.000 0

Answer 15

--取小数位
declare @str nvarchar(1000)
set @str='adsxfa.sdfdf'
select 
case when charindex('.',@str)=len(@str)   then 0 
     when charindex('.',@str)=0           then 0
     when charindex('.',@str)<len(@str)   then len(@str)-charindex('.',@str)
end

Answer 16

select
case when  isnumeric(col)<>1  or charindex('.',col)=0 then 0
else charindex('.',(stuff(reverse(col),1,patindex('%[^0]%',REVERSE(col))-1,'')))-1 end
from tb 
/*
-----------
0
0
0
3
2
0*/