convert char to money - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

convert char to money

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-01 : 08:31:38

Hi,

I have a char field called Actuals that I want to convert to money.
select convert(money,Actual ) Actual from FinanceFactTable_temp

However, i get the following error:
Cannot convert a char value to money. The char value has incorrect syntax.

How can i convert char to money?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-01 : 08:35:59

Yes, use the CONVERT funtion described in Books Online
under CAST & CONVERT.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-01 : 08:40:31

Probably you have some data in char column which is not in proper format.

Find out non-numeric data using:

select * from table where column1 not like '%[^0-9]%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-01 : 09:08:43

Yes, I think you are correct.
In the Actual field i have the value (23.67).
How can I tell the query that if the number is in brackets () to add a minus to the number and to delete the brackets so that the result will be -23.67?

Thanks

This is the structure of my table
USE [AHT_DW]
GO
/****** Object: Table [dbo].[FinanceFactTable_temp] Script Date: 10/01/2007 15:00:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FinanceFactTable_temp](
[AccountNumber] [bigint] NOT NULL,
[Date] [datetime] NOT NULL,
[CompanyCode] [int] NOT NULL,
[Actual] [nvarchar](100) COLLATE Hebrew_CI_AS NULL,
[Target] [money] NULL
) ON [PRIMARY]

I only have one row in the table:

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-01 : 09:13:44

[code]Select '-' + Replace(Replace(Column, '(', ''), ')', '')
from Table
Where column like '(%)'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-01 : 09:27:19

Thanks

for the great help

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 09:42:10

To allow for a single decimal point you probably need


select * from table where column1 not like '%[^0-9.]%' OR column1 LIKE '%.%.%'

Might also need to allow for leading "+" or "-"

Kristen

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-10-01 : 09:56:57

or just

where ISNUMERIC(yourcolumn)=1

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-01 : 09:59:48

I now have the following values in Actual field:
-23.67
-1.703,086.70
-5333,67

When I run your query:
select * from table where column1 not like '%[^0-9]%'

I get only one return value:-1.703,086.70

How can I convert the values to money. Money does accept minus as far as I know and also commas and fullstops so why do i still get the same error?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-01 : 10:04:54

Because you have two decimal point in -1.703,086.70.

See kristen's suggestion

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-01 : 10:11:33

I am sorry but I don't understand Kristen's reply really and I am not sure how it will help me cast from char to money.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 12:04:18

"where ISNUMERIC(yourcolumn)=1"

Except for the fact that IsNumeric is pretty brain damaged!

I've had a go at an IsMoney function:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90279

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 03:09:18

quote:
Originally posted by evilDBA

or just

where ISNUMERIC(yourcolumn)=1

Note that Isnumeric() is not reliable



select * from
(
	select '12.234' as data union all
	select '13d2' as data union all
	select '$123' as data union all
	select '12e3' as data 
) as t
where isnumeric(data)=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources