Conversion from text to money in a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion from text to money in a query

Author

Topic

paolomanfrin
Starting Member

6 Posts

Posted - 2006-06-21 : 10:55:20

Hi...

I've this type of data: 148.667,31 or 344.207.6 ... type text.
I need to convert it to money in a query...

How I can do this?

Thanks...

Paolo Manfrin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-21 : 11:02:07

Why did you use TEXT datatype to store money?
Use Proper Money datatype

You have to use something like

select cast(replace(cast(column as varchar(8000)),',','') as money) from yourTable

Madhivanan

Failing to plan is Planning to fail

paolomanfrin
Starting Member

6 Posts

Posted - 2006-06-21 : 11:10:14

I use it because I found It!!! The data are stored in text mode... I don't know why!
Thanks...

paolomanfrin
Starting Member

6 Posts

Posted - 2006-06-21 : 11:41:18

Perfect... your is not correct but this...

SELECT cast ( replace ( replace ( cast ( Bilancio as varchar ),'.',''),',','.' ) as money ) As Bilancio

from Dati_Clienti

yes!!! And no Logic Mistakes...

ThankYou!
Paolo

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 03:26:55

quote:
Originally posted by paolomanfrin

I use it because I found It!!! The data are stored in text mode... I don't know why!
Thanks...

If possible change the column datatype to money

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-22 : 03:52:54

if you did not specify the length for the varchar, the default is 30 (i think). You might loose some data there.

SELECT cast ( replace ( replace ( cast ( Bilancio as varchar(8000) ),'.',''),',','.' ) as money ) As Bilancio

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 04:04:29

>>if you did not specify the length for the varchar, the default is 30 (i think).

Yes it is (only when used in conversion)
If you declare without length default is 1

Declare @v varchar
set @v='Test'
Select @v

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources