Converting Nvarchar to float

1

I Have an input Sql table where an amount is stored as nvarchar(50)
I am inserting the complete record to another table where the amount is float.

I am using:

INSERT INTO Table_output(......... amount.........)
SELECT ........ ,
convert(float,replace([amount],'.'.'') ) as amount,
..........
FROM Table_input

This is not working, getting error;
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ''.

Do you need to pre-format the amount as float before carrying out the convert?

Many thanks
ash

2

If it's not a typo ,

[quote="Ash1807, post:1, topic:5337"]
replace([amount],'.'.'')
[/quote]then yours replace statement is wrong. Use this:

 replace([amount],'.'
 ,
'')
1 Like
3

If you do this (after applying @stepson's suggestion, it will remove the decimal point - which means that if you have any digits after the decimal point, your data is going to be scaled in perhaps ways that you don't want it to. For example,

73.0 will become 730
73.157 will become 73157

and so on. Did you perhaps intent to replace thousands separators? If so, use

REPLACE([amount], ',' , '')
1 Like
4

Grrr!

Thanks for all the suggestions but I still am unable to get correct syntax to work
I am pasting my complete INSERT script. The errors are the converts

INSERT INTO F_ContractAcq (ContractNo, ContractName, Reference, SalesRep, UID, Acq_Date, Start_Date, End_Date,
Termlength, TermType, ContCur, AcqInRpt, PostedInRpt, BacklogInRpt, HomeCur, AcqInHome,
PostedInHome, BacklogInHome, Region)
SELECT left(ContractNo,9) as ContractNo,
left(ContractName,50) as ContractName,
left(Reference,50) as Reference,
left(SalesRep, 9) as SalesRep,
left(UID,6) as UID,
convert(datetime, Acq_Date,5) as Acq_Date,
convert(datetime, Start_Date,5) as Start_Date,
convert(datetime, End_Date,5) as End_Date,
left(TermLength,3) as Termlength,
left(TermType, 8) as TermType,
left(ContCur,3) as ContCur,
convert(float,replace([AcqInRpt],'.','') )as AcqInRpt,**
convert(float,replace([PostedInRpt],'.','') )as PostedInRpt,**
convert(float,replace([BacklogInRpt],'.','') )as BacklogInRpt,**
left(HomeCur,3) as HomeCur,--
convert(float,replace([AcqInHome],'.','') )as AcqInHome,--
convert(float,replace([PostedInHome],'.','') )as PostedInHome,
convert(float,replace([BacklogInHome],'.','') )as BacklogInHome,
left(Region,3) as Region
FROM B_ContractAcq
WHERE ContractNo not in (SELECT ContractNo from F_ContractAcq)

All the suggestions to date are much appreciated

reg
ash

5

These are the fields that are with problems, meaning that are problems with convert to float data type.

convert(float,replace([AcqInRpt],'.','') )as AcqInRpt,**
convert(float,replace([PostedInRpt],'.','') )as PostedInRpt,**
convert(float,replace([BacklogInRpt],'.','') )as BacklogInRpt,**

Can you post the error message , at least one of it ?

Starting with Sql Server 2012 , you can use TRY_CONVERT : TRY_CONVERT(float,replace([BacklogInHome],'.',''))

Then check where you have NULL for this values, and then take a look of the raw data in these fields.