how to change the decimal symbol

Hi , i have a problem i live in Europe (Spain) and we use
the " , " symbol instead of " . " as decimal symbol for
money. Actually my problem is that i have a table with
some varchar fields storing money values but when i try to
use them in stored procedures as money using Cast(field as
money) sql Server don't use the " , " symbol as decimal
and the values i get are useless.
How can i set up the sql server to change the , for the .?
thanks in advance.
hi Jorge
"Jorge Lozano" <capikirk@.terra.es> ha scritto nel messaggio
news:189101c4bc43$eb5bcff0$a501280a@.phx.gbl
> Hi , i have a problem i live in Europe (Spain) and we use
> the " , " symbol instead of " . " as decimal symbol for
> money. Actually my problem is that i have a table with
> some varchar fields storing money values but when i try to
> use them in stored procedures as money using Cast(field as
> money) sql Server don't use the " , " symbol as decimal
> and the values i get are useless.
> How can i set up the sql server to change the , for the .?
> thanks in advance.
first of all, you should not pass decimal as chars, but you should treat
them as numeric values..
SQL Server correctly understand only the dot "." as decimal place holder
and, as it's not a reporting tool but a data tool, it just pass them in and
out that way.. no thousend placeholder and "." for decimal
befor casting it, you should convert decimal sep to "."..
SET NOCOUNT ON
DECLARE @.n VARCHAR(14)
DECLARE @.dec DECIMAL (14,4)
PRINT 'this number, 123456.1234 is formatte in "italian" style =;-D'
SET @.n = '123.456,1234'
SELECT @.n AS [Original]
PRINT 'remove thousend placeholder'
SET @.n = REPLACE( @.n , '.' , '')
SELECT @.n AS [thousand stripped]
PRINT 'replace decimal placeholder'
SET @.n = REPLACE( @.n , ',' , '.')
SELECT @.n AS [Final string]
PRINT 'cast it to numeric value'
SET @.dec = CONVERT(DECIMAL(14,4), @.n)
SELECT @.dec AS [Casted decimal value]
PRINT 'in one single step'
SET @.n = '123.456,1234'
SELECT CONVERT(DECIMAL(14,4), REPLACE( REPLACE( @.n , '.' , '') , ',' , '.'))
AS [Final value]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply