Conversion of varchar to float

[Steven]

Hello,

I have a varchar field with for example following entry: "000001152,00". This data is being downloaded from a txt file.
I want to convert this to 1152. Is this something I can do during the download process? And if so, how?

[skhanal]

How are you downloading the file? What tool are you using. The method to convert depends on the tool

In plain T-SQL this can be done as

select cast(replace('000001152,000',',','.') as float)

[Steven]

hi skhanal,
Well, the files are on a secured FTP server, and are downloaded thru a DTS package (i.e."Text File (source)") and then just copied to a table. How should I then convert it?

[rmiao]

You can load data into staging table, convert the data with above code, then move them to target table.

[Steven]

good tip!!! txs

[skhanal]

You can do the conversion in DTS as well using VBScript.

[Steven]

and do you that skhanal

[skhanal]

Change the transformation to ActiveX transformation and edit the line for the column you want to convert to

DTSDestination("Column1") = CDbl(Replace(DTSSource("Column1"),",","."))