求字符转换成int 后处理方法
有个表里面有个字段：unit ，里面大部分的值是数字，但有很小一部分有字符，如下：
id unit
12 201+1
12 20
12 321
15 3/PNL
15 /
15 23
15 658.1
要做到以下效果：
1. 有 “ + ” 符号的情况，例：201+1 要将结果加在一起：202
2. 发现数字有小数点的，省去小数点。
3. 有 “ /PNL ” 或者 “ / PNL ”的情况，替换成空。如果是：3/PNL ,要得到 3；“ /PNL ”,得到0 。
4. 有 “ / ” 符号的情况（单独），替换成空，或者0.
5. 1，4丙种情况处理后，将数据加起来，再乘以一个数字，比如 2.
6. 2，3两种情况处理后，将数据加起来即可。
7. 将5，6得到的数值求和。
最终效果应该是：
id unit
12 1086 --（202 + 20 + 321）* 2=1086
15 1365 --（658 + 23）*2 + 3 + 0 =1365
[解决办法]
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-11-22 16:44:32
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[unit] varchar(5))
insert [tb]
select 12,'201+1' union all
select 12,'20' union all
select 12,'321' union all
select 15,'3/PNL' union all
select 15,'/' union all
select 15,'23' union all
select 15,'658.1'
--------------开始查询--------------------------
IF object_id('tempdb..#tb') IS NOT NULL
DROP TABLE #tb
go
CREATE TABLE #tb
(
[id] int,[unit] varchar(5)
)
go
INSERT INTO #tb SELECT id,unit FROM tb WHERE LEN(unit)-LEN(REPLACE(REPLACE(REPLACE(unit,'/PNL',''),'.',''),'/',''))>0
SELECT id,
CASE WHEN CHARINDEX('/PNL',unit)>0 THEN LEFT(unit,CHARINDEX('/PNL',unit)-1)
WHEN CHARINDEX('/',unit)>0 THEN 0
WHEN CHARINDEX('.',unit)>0 THEN CAST(REPLACE(unit,'.','')/10 AS INT)
END AS unit INTO #tb1 FROM #tb
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select ID='+ltrim(ID)+', unit=2*('+unit+') union all ' from (SELECT * FROM tb a WHERE NOT EXISTS(SELECT 1 FROM #tb WHERE unit=a.unit))a
set @sql=left(@sql,len(@sql)-10)
SET @sql='select id,sum(unit) as unit from(select * from ('+@sql+') as a union all (select * from #tb1))t group by id'
EXEC(@sql)
DROP TABLE #tb1
----------------结果----------------------------
/* id unit
----------- -----------
12 1086
15 707
(2 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[unit] varchar(5))
insert [tb]
select 12,'201+1' union all
select 12,'20' union all
select 12,'321' union all
select 15,'3/PNL' union all
select 15,'/' union all
select 15,'23' union all
select 15,'658.1'
;with t
as
(
select case when CHARINDEX('/PNL',unit) >0
then case when REPLACE(unit,'/PNL','') =''
then '0'
else REPLACE(unit,'/PNL','')
end
when CHARINDEX('/',unit) >0
then case when REPLACE(unit,'/','') =''
then '0'
else REPLACE(unit,'/','')
end
when CHARINDEX('+',unit) >0
then cast(cast(LEFT(unit,CHARINDEX('+',unit)-1) AS int)+
RIGHT(unit,len(unit)-CHARINDEX('+',unit)) as varchar)
when CHARINDEX('.',unit)>0
then LEFT(unit,CHARINDEX('.',unit)-1)
else unit
end as unit,
id,
case when CHARINDEX('/PNL',unit) >0
then 1
when CHARINDEX('/',unit) >0
then 1
when CHARINDEX('+',unit) >0
then 0
when CHARINDEX('.',unit)>0
then 0
else 0
end as flag
from tb
)
select id,SUM(v) as v
from
(
select id,SUM(CAST(unit as int)) * 2 as v
from t
where flag = 0
group by id
union all
select id,SUM(CAST(unit as int)) as v
from t
where flag = 1
group by id
)tt
group by id
/*
idv
121086
151365
*/
if object_id('tempdb..#tb') is not null drop table #tb
go
create table #tb([id] int,[unit] varchar(5))
insert #tb
select 12,'201+1' union all
select 12,'20' union all
select 12,'321' union all
select 15,'3/PNL' union all
select 15,'/' union all
select 15,'23' union all
select 15,'658.1'
declare @sql varchar(max)
with temptable as (
select [id],[unit],[type],[value],case [type] when 1 then '+2*('+[value] when 4 then '+2*(' else '+'+[value] end as [compute]
from(
select [id],[unit],case when CHARINDEX('+',unit)>0 then 1 when CHARINDEX('.',unit)>0 then 2
when CHARINDEX('PNL',unit)>0 then 3 when CHARINDEX('/',unit)>0 then 4 else 2 end as [type],
case when CHARINDEX('+',unit)>0 then unit when CHARINDEX('.',unit)>0 then LEFT(unit,charindex('.',unit)-1)
when CHARINDEX('PNL',unit)>0 then case when CHARINDEX(' /',unit)>0 then '0' else LEFT(unit,CHARINDEX('/',unit)-1) end
when CHARINDEX('/',unit)>0 then '0' else unit end as [value]
from #tb
)t1
)
select @sql=ISNULL(@sql + ' UNION ALL ','')+' select '+ convert(varchar(10),[id])+','+[compute]
from (
select [id],[compute] = (select [compute]+'' from #tb1 b where a.[id]=b.[id] for xml path(''))+
replicate(')',SUM(case when charindex('(',[compute])>0 then 1 else 0 end)) from temptable a
group by [id]
)t
exec(@sql)
