[dba-SQLServer] Convert nvarchar to money

...or, as it pertains to your case:

insert Table2 select AcctNum, dbo.fn_CnvNVarChar2Money(Amount) from
Table1


Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Djabarov, Robert
Sent: Wednesday, February 25, 2004 1:32 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Convert nvarchar to money

Absolutely, except I'd create a function that would handle all that for
you:

create function dbo.fn_CnvNVarChar2Money (
   @amt nvarchar(100) ) returns money as
begin
   declare @Multiplier int
   if charindex('-', @amt) > 0
      set @Multiplier = -1
   else
      set @Multiplier = 1

   return (cast(replace(replace(@amt, ',', ''), '-', '') as money) *
@Multiplier)
end
go
select dbo.fn_CnvNVarChar2Money('50.00-')


Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark
Boyd
Sent: Wednesday, February 25, 2004 1:11 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Convert nvarchar to money

I'm still having problems.
I've tried each suggestion, but unfortunately I still receive an error
regarding incorrect syntax.
The data from Table1 has values of 175.00, 50.00-, and .00.
Do I need to include additional criteria in the replace function to
account for these formats?

Thanks,
Mark

-----Original Message-----
From: Djabarov, Robert [mailto:Robert.Djabarov at usaa.com] 
Sent: Wednesday, February 25, 2004 1:54 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Convert nvarchar to money

You may also have to take into account the value formatting in your
NVARCHAR field, like thousand-separator characters, as well as decimal
separators.  If, for example, the field contains 2,342,985.3400 you'll
need to do something like this:

cast(replace('2,342,985.3400', ',', '') as money)

If the value has period as thousand-separator and a comma for a decimal,
you'll need to do a reverse:

cast(replace(replace('2.342.985,3400', '.', ''), ',', '.') as money)

Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark
Boyd
Sent: Wednesday, February 25, 2004 12:11 PM
To: SQLServerList
Subject: [dba-SQLServer] Convert nvarchar to money

I am trying to append data from Table1 to Table2.

In Table1, the field [Amount] is datatype 'nvarchar'.

In Table2, the field [Amount] is datatype 'money'.

 

I am using the following SQL query to run the append: "INSERT INTO
Table2 SELECT AcctNum, Amount FROM Table1".

I guess I need to use the CONVERT function, but can't figure out how
this is done.

Any examples would be greatly appreciated.

 

Thanks,

Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc.

 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com