一個SQL查詢問題，立即解決，立即給分

Question

PID      DESC                               
P0001    18KW 3.86GM 54DFB0.51 78DFA0.51    
P0002    14KW 1.83GM 11DFA0.06 1PEA3.86     
P0003    14KW 14DFA0.08 1PEA5.86     得出效果如下：只要有GM单位的数字拿出来做一列,没有GM的就为0PID      DESC                               QTYGOLD
P0001    18KW 3.86GM 54DFB0.51 78DFA0.51    3.86
P0002    14KW 1.83GM 11DFA0.06 1PEA3.86     1.83
P0003    14KW 14DFA0.08 1PEA5.86            0

Answer 1

select t.* , case when [desc] like '%GM%' then substring([desc] , charindex(' ' , [desc]) + 1 , charindex('GM' , [desc]) - charindex(' ' , [desc])) else 0 end from tb

Answer 2

create table tb(PID varchar(10),[DESC] varchar(50))   
insert into tb values('P0001', '18KW 3.86GM 54DFB0.51 78DFA0.51')
insert into tb values('P0002', '14KW 1.83GM 11DFA0.06 1PEA3.86')   
insert into tb values('P0003', '14KW 14DFA0.08 1PEA5.86')
goselect t.* , QTYGOLD = (case when [desc] like '%GM%' then cast(substring([desc] , charindex(' ' , [desc]) + 1 , charindex('GM' , [desc]) - charindex(' ' , [desc]) - 1) as decimal(18,2)) else 0.00 end) from tb tdrop table tb   /*
PID        DESC                                               QTYGOLD              
---------- -------------------------------------------------- -------------------- 
P0001      18KW 3.86GM 54DFB0.51 78DFA0.51                    3.86
P0002      14KW 1.83GM 11DFA0.06 1PEA3.86                     1.83
P0003      14KW 14DFA0.08 1PEA5.86                            .00（所影响的行数为 3 行）
*/

Answer 3

create table tb(PID varchar(10),[DESC] nvarchar(50))
insert into tb select 'P0001','18KW 3.86GM 54DFB0.51 78DFA0.51'
insert into tb select 'P0002','14KW 1.83GM 11DFA0.06 1PEA3.86'   
insert into tb select 'P0003','14KW 14DFA0.08 1PEA5.86'
go
select *,(case when charindex('GM ',[desc])=0 then '0'
else substring([desc],charindex('KW ',[desc])+3,charindex('GM ',[desc])-charindex('KW ',[desc])-3) end)QTYGOLD
from tb
/*
PID        DESC                                               QTYGOLD
---------- -------------------------------------------------- --------------------------------------------------
P0001      18KW 3.86GM 54DFB0.51 78DFA0.51                    3.86
P0002      14KW 1.83GM 11DFA0.06 1PEA3.86                     1.83
P0003      14KW 14DFA0.08 1PEA5.86                            0(3 行受影响)
*/
go
drop table tb

Answer 4

create table tb(PID varchar(10),[DESC] varchar(50))   
insert into tb values('P0001', '18KW 3.86GM 54DFB0.51 78DFA0.51')
insert into tb values('P0002', '14KW 1.83GM 11DFA0.06 1PEA3.86')   
insert into tb values('P0003', '14KW 14DFA0.08 1PEA5.86')
goselect pid,[desc],
cast(SUBSTRING([desc],CHARINDEX(' ',[desc],0),case when CHARINDEX('GM',[desc],0)-CHARINDEX(' ',[desc],0)<=0 then 0 else CHARINDEX('GM',[desc],0)-CHARINDEX(' ',[desc],0) end) as float)
from tb;