convert varchar to int in select statement using patindex

Question

My query is for convert varchar into string,

select top(5)'Insert into jobs(minexperience,maxexperience)values('+

cast(substring(Experience as varchar(50)),0,patindex('%to%',Experience))*365*24*60*60,

cast(substring(Experience as      

varchar(50)),patindex('%to%',Experience)+2,patindex('%Years%',Experience)-patindex('%to%',Experience)-2)*365*24*60*60+')'

from requirementsdetailsfororganization

In my below query i have an error "Incorrect syntax near the keyword 'AS'."

I want to convert string to integer.

Any Help?

Use `substring(cast(Experience as ....` instead of `cast(substring(Experience as....` — praveen, Apr 16 '13 at 11:51
Also TOP 5 without an ORDER BY? So you don't care which 5 you get? And why are you storing unreliable strings like `1997 to 2005` to indicate experience duration? Why not store 1997 and 2005 in separate columns? — Aaron Bertrand, Apr 16 '13 at 12:05
i tried switch. but same error. I take values from old database and execute my new database. Old database created like that. — PoliDev, Apr 16 '13 at 12:09

score 2 · Accepted Answer · answered Apr 17 '13 at 06:06

Possible this helpful for you -

SELECT TOP(5) 'INSERT INTO dbo.jobs(minexperience,maxexperience) VALUES(' +
    CAST(SUBSTRING(
          CAST(r.Experience AS VARCHAR(50))
        , 0
        , r.ToExperience) * 31536000 
    AS VARCHAR(50))

    + ',' +

    CAST(SUBSTRING(
          CAST(r.Experience AS VARCHAR(50))
        , r.ToExperience + 2
        , patindex('%Years%', r.Experience) - r.ToExperience - 2) * 31536000 
    AS VARCHAR(50))
+')'
FROM (
    SELECT 
          r.Experience
        , ToExperience = PATINDEX('%to%', r.Experience)
    FROM dbo.requirementsdetailsfororganization r
) r

convert varchar to int in select statement using patindex

1 Answers1