Add values show into single column in sql server

Question

In my table, I have Experience field like

Experience

5Years0Months
2Years0Months

Here I want to convert into seconds then add Years and Months into a single column.

Experience - [Some value]

So i create one query like following,

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

From the above code i have the result like,

Experience

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

Expected Result

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

How to do that in my query? Help me anyone?

score 1 · Answer 1 · answered Apr 20 '13 at 11:13

What you want is to turn something like this (currently generated)

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

To this, which will sum them

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

Which looks like changing your original code similarly:

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

score 1 · Answer 2 · answered Apr 20 '13 at 11:18

I found the answer,

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

Add values show into single column in sql server

2 Answers2