CODEWITHSUNDEEP
sqlsql-server-2008selectcastingsubstring
PoliDev
PoliDev

Reputation: 1458

Create select query how to convert varchar to int by using cast

I want to convert the values

2to5experience

3to7experience

to Minexperience:

2
3

Maxexperience

5
7

So i create the following query,

select cast(substring(Cast(Experience AS VARCHAR(50)),1,1)*365*24*60*60 as int) as Experience 
      from requirementsdetailsfororganization

The above one is working. when i update the query like following, I am getting an error.

select 'Insert into Jobs(requirementId,organizationId,Minexperience)values(' + 
   Cast(o.RequirementID as varchar(50))+',' +
   Cast(o.OrganizationID as varchar(50))+',' +
   cast(substring(CAST(Experience AS VARCHAR(50)),1,1)*365*24*60*60 as int)+')'
   FROM RequirementsDetailsforOrganization AS o

It shows error

"Conversion failed when converting the varchar value ')' to data type int"

Any idea?

Upvotes: 1

Views: 3593

Answers (2)

Marimuthu Kandasamy
Marimuthu Kandasamy

Reputation: 516

Try with following Steps.

--Create Table :
Create Table #Table
(
Name Varchar(50),
Experience Varchar(20)
)
Go

-- Insert Values :
Insert into #Table Values('Tom','0to0experience')
Insert into #Table Values('Victor','0to1experience')
Insert into #Table Values('Mark','11to12experience')
Go

--View Data
Select  * from #Table

--Using CharIndex  and Substring :
Select Name,
Substring(Experience,1,CharIndex('to',Experience)-1) as Yearfrom,
Substring(Experience,(CharIndex('to',Experience)+2),Len(Replace(Experience,'experience','')) - (CharIndex('to',Experience)+1)) as YearTo
from #Table

--Clean Up:
Drop Table #Table

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

In the second to last row in your select you cast the expression to int and then add (using +) a ). Since the value on the left of the + is an integer, SQL Server tries to convert the operand to the right of the + to an integer too.

Try changing the cast to int so that you instead cast it to varchar:

select 'Insert into Jobs(requirementId,organizationId,Minexperience)values(' + 
   Cast(o.RequirementID as varchar(50))+',' +
   Cast(o.OrganizationID as varchar(50))+',' +
   cast(substring(CAST(Experience AS VARCHAR(50)),1,1)*365*24*60*60 as varchar(50))+')'
   FROM RequirementsDetailsforOrganization AS o

Note:

The cast of Experience to varchar is probably superfluous.

Upvotes: 2

Related Questions