charindex('.', cast(money as varchar(100))) between 1 and 4
--try --1 with a as ( select 1 id,'753261.210' [money] union all select 2,'52712.21' union all select 3,'2167.95' union all select 4,'125.970' union all select 5,'80172.2569' ) select * from a where LEN(CEILING (CONVERT(float,[MONEY]))) < 4 --result: id money ----------- ---------- 4 125.970 (1 行受影响) --2 with a as ( select 1 id,'753261.210' [money] union all select 2,'52712.21' union all select 3,'2167.95' union all select 4,'125.970' union all select 5,'80172.2569' ) select * from a where LEN(CONVERT(INT, CEILING([money]) )) < 4--result: id money ----------- ---------- 4 125.970(1 行受影响)
--try --3 with a as ( select 1 id,'qwew.dfs' [money] union all select 2,'asdfwer23.0pq' union all select 3,'1sdd.uod' union all select 3,'1sd.uod' ) select * from a where PATINDEX('%.%',[money]) < 5 --注意:因该函数包含比较字符故不是小于4而是5。
--result: id money ----------- ------------- 3 1sd.uod(1 行受影响)
--> --> (Roy)生成測試數據
declare @T table([id] int,[money] decimal(18,4)) Insert @T select 1,753261.210 union all select 2,52712.21 union all select 3,2167.95 union all select 4,125.970 union all select 5,80172.2569
Select * from @T where cast([money]/1000 as int)=0 and cast([money] as int)>0/* 4 125.9700 */
2 asdfwer23.0pq
3 1sdd.uod
--1
with a as (
select 1 id,'753261.210' [money] union all
select 2,'52712.21' union all
select 3,'2167.95' union all
select 4,'125.970' union all
select 5,'80172.2569'
)
select *
from a
where LEN(CEILING (CONVERT(float,[MONEY]))) < 4 --result:
id money
----------- ----------
4 125.970 (1 行受影响)
--2
with a as (
select 1 id,'753261.210' [money] union all
select 2,'52712.21' union all
select 3,'2167.95' union all
select 4,'125.970' union all
select 5,'80172.2569'
)
select *
from a
where LEN(CONVERT(INT, CEILING([money]) )) < 4--result:
id money
----------- ----------
4 125.970(1 行受影响)
--3
with a as (
select 1 id,'qwew.dfs' [money] union all
select 2,'asdfwer23.0pq' union all
select 3,'1sdd.uod' union all
select 3,'1sd.uod'
)
select *
from a
where PATINDEX('%.%',[money]) < 5 --注意:因该函数包含比较字符故不是小于4而是5。
--result:
id money
----------- -------------
3 1sd.uod(1 行受影响)
declare @T table([id] int,[money] decimal(18,4))
Insert @T
select 1,753261.210 union all
select 2,52712.21 union all
select 3,2167.95 union all
select 4,125.970 union all
select 5,80172.2569
Select *
from @T
where cast([money]/1000 as int)=0 and cast([money] as int)>0/*
4 125.9700
*/