Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 757
  • Last Modified:

Convert from Nvarchar to Float

select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On SR.[ID Number]= A.Applicant_IDNumber

Open in new window


Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

Hi  i need to convert Applicant_IDNumber to a float(currently it is a Nvarchar) [ID Number] is a float if i convert this to a nvachar it picks up no matches when there are matches

0
Davesm
Asked:
Davesm
1 Solution
 
AngelgeoCommented:
select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On SR.[ID Number]= cast(A.Applicant_IDNumber as float)
0
 
DavesmAuthor Commented:
Hi i still get this error

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On CAST(SR.[ID Number] AS NVARCHAR)= A.Applicant_IDNumber

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bhavesh ShahLead AnalysistCommented:
Hi,

try this one
select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On cast(SR.[ID Number] as varchar)= cast(A.Applicant_IDNumber as varchar)

Open in new window

0
 
AngelgeoCommented:
select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On SR.[ID Number]= cast(replace(A.Applicant_IDNumber,',','.') as float)

or

select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On SR.[ID Number]= cast(isnull(replace(A.Applicant_IDNumber,',','.'),0) as float)
0
 
Bhavesh ShahLead AnalysistCommented:
Hi Angelgeo,

Small note to take....

Its nvarchar column, so column could contain any characters..... =)


- Bhavesh
0
 
OP_ZaharinCommented:
hi Davesm,

- this code use a CONVERT() function. since ID Number itself is a FLOAT, we convert the A.Applicant_IDNumber to FLOAT. do give it a try:

select Applicant_IDNumber,[ID Number]
from Applicant A
join  mis.dbo.Staff_Report SR On SR.[ID Number] = CONVERT(FLOAT, A.Applicant_IDNumber)

Open in new window

0
 
LowfatspreadCommented:
select applicant_idnumber,[id number]
from (select convert(float,applicant_idnumber) as applicant_idnumber
 from applicant where isnumeric(applicant_idnumber)=1) as a
inner join mis.dbo.Staff_Report SR On SR.[ID Number] = A.Applicant_IDNumber

but it is not a sensible arrangement to have ids using floating point numbers....
or to mix char and float datatypes like this....
















0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now