Problems with Convert-ing VarChar to Numeric 1

[danhare]

Hi
I am trying to get a text string that i am parsing using SubString into a numeric type field.
The number is in the form ;
3,145,554.00

After reading Books Online, I have tried the following syntax in Query Analyzer ;

select convert(numeric , '3,145', 2)

This returns the error ;
Error converting data type varchar to numeric.

Does anyone have any ideas please ?

dan, uk

 

[foxdev]

You might try first applying the Replace function on the string to strip out the commas. Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[danhare]

Thanks Robert - i am a bit new still to sp's therefore dont know all the functions that i can use.
however, am i wrong in thinking that the CONVERT syntax that i used SHOULD have worked ?

Thanks again for you amazingly prompt reply.

dan

 

[foxdev]

We might all agree that it would be nice if [tt]convert(int, '23,232')[/tt] worked, but it doesn't work by design (and it doesn't work in Sybase either). Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[tlbroadbent]

Try double conversion character --> money --> decimal.

select convert(decimal(12,2),convert(money,'3,145,554.00'))

OR

select cast(cast('3,145,554.00' as Money) As decimal(12,2)) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[danhare]

Thanks guys.
The conversion to Money type worked fine - my inexperience of the myriad data types i guess !

thanks again.

dan