--建表
create table #tb([rev] varchar(200),[mon] money)
go
 insert into #tb values              
('(A) $0  - $8,248 ',    0.00),
('(A) $0  - $8,248 ',    55.08),
('(A) $0  - $8,248 ',    90000.12),--不在，（A）
('(A) $0  - $8,248',     0.00),
('(A) $0  - $8,248',     0.00),
('(C) $30,455  - $91,850',     48888.728),
('(B) $8,249  - $30,419',     103079.28),--不在，（B）
('(A) $0  - $8,248 ',    0.00),
('(A) $0  - $8,248 ',    0.00),
('(D) $91,929  - $16,905,130 ',    306454.166),
('(B) $8,249  - $30,419',     13.38),--不在，（B）
('(A) $0  - $8,248',     0.00),
('(D) $91,929  - $16,905,130 ',    8989171792.96);--不在，（D）

--要求：统计[mon]的数值不在[rev]对应的区间中的个数？
/*
结果为：
[type]  [num]
 (A)      1
 (B)      2
 (C)      0
 (D)      1  
*/

select type,sum(case when mon between num1 and num2 then 0 else 1 end) as num
from
(
select left(rev,charindex(')',rev)) as type,
  cast(replace(substring(rev,charindex('$',rev)+1,charindex('- $',rev)-charindex('$',rev)-1),',','') as dec(18,2)) as num1,
  cast(replace(right(rev,charindex('$',reverse(rev))-1),',','')  as dec(18,2)) as num2,
  mon
from #tb
) t
group by type
/**
type       num
---------- -----------
(A)        1
(B)        2
(C)        0
(D)        1

(4 行受影响)
**/

drop table #tb

------解决方案--------------------

SQL code
create table #tb([rev] varchar(200),[mon] money)
go
 insert into #tb values              
('(A) $0  - $8,248 ',    0.00)
 insert into #tb values         
('(A) $0  - $8,248 ',    55.08)
 insert into #tb values         
('(A) $0  - $8,248 ',    90000.12)--不在，（A）
 insert into #tb values         
('(A) $0  - $8,248',     0.00)
 insert into #tb values         
('(A) $0  - $8,248',     0.00)
 insert into #tb values         
('(C) $30,455  - $91,850',     48888.728)
 insert into #tb values         
('(B) $8,249  - $30,419',     103079.28)--不在，（B）
 insert into #tb values         
('(A) $0  - $8,248 ',    0.00)
 insert into #tb values         
('(A) $0  - $8,248 ',    0.00)
 insert into #tb values         
('(D) $91,929  - $16,905,130 ',    306454.166)
 insert into #tb values         
('(B) $8,249  - $30,419',     13.38)--不在，（B）
 insert into #tb values         
('(A) $0  - $8,248',     0.00)
 insert into #tb values         
('(D) $91,929  - $16,905,130 ',    8989171792.96);--不在，（D）


SELECT rev,SUM(CASE WHEN mon NOT BETWEEN mi AND ma THEN 1 ELSE 0 END) AS mon
FROM (
    SELECT LEFT(rev,CHARINDEX(')',rev)) AS rev,
        CAST(RTRIM(SUBSTRING(rev,CHARINDEX(')',rev)+1,CHARINDEX('-',rev)-CHARINDEX(')',rev)-1)) AS money) AS mi,
        CAST(SUBSTRING(rev,CHARINDEX('-',rev)+1,8000) AS money) AS ma,mon
    FROM #tb
) AS A
GROUP BY rev


DROP TABLE #tb

------解决方案--------------------

SQL code

create table #tb([rev] varchar(200),[mon] money)
go

insert into #tb values              
('(A) $0  - $8,248 ',    0.00),
('(A) $0  - $8,248 ',    55.08),
('(A) $0  - $8,248 ',    90000.12),--不在，（A）
('(A) $0  - $8,248',     0.00),
('(A) $0  - $8,248',     0.00),
('(C) $30,455  - $91,850',     48888.728),
('(B) $8,249  - $30,419',     103079.28),--不在，（B）
('(A) $0  - $8,248 ',    0.00),
('(A) $0  - $8,248 ',    0.00),
('(D) $91,929  - $16,905,130 ',    306454.166),
('(B) $8,249  - $30,419',     13.38),--不在，（B）
('(A) $0  - $8,248',     0.00),
('(D) $91,929  - $16,905,130 ',    8989171792.96);--不在，（D）


select t.le '[TYPE]',sum(t.st) '[NUM]'
from
(select substring(rev,patindex('%([A-Z])%',rev),3) le,
case when mon between
cast(substring(rev,patindex('%) $%',rev)+3,charindex('-',rev)-patindex('%) $%',rev)-3) as money)
and
cast(substring(rev,patindex('%- $%',rev)+3,200) as money)
then 0 else 1 end st
from #tb) t
group by t.le


[TYPE] [