将值show添加到sql server的单列中

Question

在我的表中，我有经验字段，如

经验

5 years0months
2 years0months

这里我想转换成秒，然后将年和月添加到单列中。

Experience - [Some value]

所以我创建了一个如下的查询，

select top(10)'insert into candidates(experience)values('+
CAST(SUBSTRING(CAST(o.Experience AS VARCHAR(50)), 0, PATINDEX('%Years%', o.Experience))      * 31536000 AS VARCHAR(50))
           +','+CAST(SUBSTRING(CAST(o.Experience AS VARCHAR(50)), PATINDEX('%Years%', o.Experience) + 5
        ,patindex('%Months%', o.Experience) - PATINDEX('%Years%', o.Experience) - 5) * 
2678400 AS VARCHAR(50))+')'
            from candidatedetails as o

从上面的代码中，我得到了这样的结果:

insert into candidates(experience)values(157680000,0)
insert into candidates(experience)values(31536000,26784000)

预期结果

 insert into candidates(experience)values(157680000)
 insert into candidates(experience)values(58320000)//add(31536000+26784000)

如何在我的查询中做到这一点?有人能帮我吗?

Answer 1

你想要的是把这样的东西(当前生成的)

insert into candidates(experience)values(157680000,0)
insert into candidates(experience)values(31536000,26784000)

到this，它将它们相加

insert into candidates(experience) select 157680000+0;
insert into candidates(experience) select 31536000+26784000;

这看起来像改变你的原始代码类似:

select top(10) 'insert into candidates(experience) select '+
        CAST(SUBSTRING(CAST(o.Experience AS VARCHAR(50)), 0,
             PATINDEX('%Years%', o.Experience)) * 31536000 AS VARCHAR(50))
   +'+'+CAST(SUBSTRING(CAST(o.Experience AS VARCHAR(50)),
             PATINDEX('%Years%', o.Experience) + 5,
             patindex('%Months%', o.Experience)
           - PATINDEX('%Years%', o.Experience) - 5) * 2678400 AS VARCHAR(50))+';'
from candidatedetails as o

Answer 2

我找到答案了

SELECT TOP(10) 'INSERT INTO jobs(Experience) VALUES('+
        CAST(SUBSTRING(CAST(r.experience AS VARCHAR(50)), 0, PATINDEX('%Years%', r.experience))*31536000
            + SUBSTRING(CAST(r.experience AS VARCHAR(50)), PATINDEX('%Years%', r.experience) + 5, 
                                                       patindex('%Months%', r.Experience) - PATINDEX('%Years%', r.Experience) - 5)* 2678400 AS VARCHAR(50))+')'
    FROM  candidatedetails r