April 4, 2005 at 3:15 pm
I am trying to insert a floating point value into the db. This value is passed as a string and has separators in it. Normally I would just strip out the commas and insert the value directly but this is now in an international context so data coming in from Florida will be '2,133.98534' and the same data coming in from Italy will be '2.133,98534'
Anyone know how to convert this? I pass it as a string and try to have SQL Server do a cast as float but keep getting a conversion error. Any ideas?
exec insertCompl 1,'2,133.98534'
---------procedure-----------------
CREATE PROCEDURE dbo.insertCompl
@tIDKEY int,
@tIDNUM float
AS
insert into tblThree(idKey,idNum) values(@tIDKEY,CAST(@tIDNUM AS float));
GO
April 4, 2005 at 3:42 pm
There might be a better way, but I don't know of a SQL Server function to auto-detect numeric format type. In this situation (I'm no expert on international numbers) you may be able to detect the right most punctuation, strip everything to the right as the remainder, strip everything to the left as the whole value, strip all punctuation from the whole value, and recombine as WholeValue + '.' + Remainder and then cast that as a float.
Good luck.
April 4, 2005 at 10:38 pm
YOu can use
Select Convert(money,'12,353.12').
This will work out
April 5, 2005 at 7:02 am
I thought of doing that but the precision only goes out to 4 decimal places. There is a possibility of more.
April 5, 2005 at 7:53 am
I just tried the following and didn't get the results I thought I could:
select cast(convert(money,'2,199.8352') as float)
result: 2199.8352 (this is good)
select cast(convert(money,'2.199,8352') as float)
result:Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
This is not good. It doesn't recognize this as a different number format.
April 5, 2005 at 8:18 am
This is basically what I referred to in my earlier post. It can also accomodate other punctuation if you add replace statements for them. Basically, step one is to convert any division symbols to periods (this example only supports commas and periods) and then treat the right most period as the decimal indicator.
declare @TheVal varchar(20)
set @TheVal = '2,199.835277'
set @TheVal = '2.199,835277'
set @TheVal = '2199835277'
set @TheVal = '2199.835277'
set @TheVal = REPLACE( @TheVal, ',', '.' )
set @TheVal = REPLACE( LEFT( @TheVal, LEN(@TheVal) - CHARINDEX( '.', REVERSE( @TheVal ) ) ), '.', '' )
+ RIGHT( @TheVal, CHARINDEX( '.', REVERSE( @TheVal ) ) )
select @TheVal AS StringVersion
, CAST( @TheVal AS float ) AS FloatVersion
April 5, 2005 at 9:05 am
Pretty slick. I implemented this as a user defined function in my stored proc. Working well. thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply