Error converting data type varchar to float - sql server - function using @string1 = @string1 + '.' + @string2
I have a sql server 2008 function with the following statement
@String1 = @String1 + '.' + @String2
I get the error message 'Error converting data type varchar to float'. If I remove the '.' I do not get the error
@String1 = @String1 + @String2 (no error on this one)
I also tried @String1 = @STring1 + convert(Varchar(1),'.') + @String2 (error on this one too)
@String1 = @String1 + '.' + @String2
I get the error message 'Error converting data type varchar to float'. If I remove the '.' I do not get the error
@String1 = @String1 + @String2 (no error on this one)
I also tried @String1 = @STring1 + convert(Varchar(1),'.') + @String2 (error on this one too)
Aneesh
can you ensure that @String1 and @String2 are actually of varchar type
What data types are @String1 and @String2? Are they actually numeric?
That is because one or both variables are set as float. It tries to convert '.' into a float and an error is thrown. Try this:
@String1 = CAST(@String1 AS varchar) + '.' + CAST(@String2 AS varchar)
@String1 = CAST(@String1 AS varchar) + '.' + CAST(@String2 AS varchar)
@String1 = cast( @String1 as varchar ) + '.' + cast( @String2 as varchar )
ASKER
Both string1 and string2 are definitely varchar
@country_extension varchar(5), (this is @string2)
@tariff_code varchar(13), (this is @string1)
I tried using cast as was suggested and it did not work. When I take out the '.' it works fine so it has something to do with the '.' being in the statement.
@country_extension varchar(5), (this is @string2)
@tariff_code varchar(13), (this is @string1)
I tried using cast as was suggested and it did not work. When I take out the '.' it works fine so it has something to do with the '.' being in the statement.
Ok, instead of the concatenation, use a:
SELECT @String1,@String2
and let us know what values are in there.
SELECT @String1,@String2
and let us know what values are in there.
ASKER
If I take out the '.' and just have
SET @String1 = @String1 + @String2
Return @String1
I get
9999.551234
(@string1 = 9999.55, @string2 = 1234)
I realize they are 'numbers' but they could have characters in them too.
SET @String1 = @String1 + @String2
Return @String1
I get
9999.551234
(@string1 = 9999.55, @string2 = 1234)
I realize they are 'numbers' but they could have characters in them too.
I have tried similar code on MS SQL 2005 and I have no such issue:
declare @String1 varchar(13),@String2 varchar(5)
set @String1=9999.55
set @String2=1234
set @String1=@String1+'.'+@Str
select @String1
returns 9999.55.1234
declare @String1 varchar(13),@String2 varchar(5)
set @String1=9999.55
set @String2=1234
set @String1=@String1+'.'+@Str
select @String1
returns 9999.55.1234
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works for me when I run it like that. I will get my code for you to see if you can duplicate.
ASKER
Ok - I just found the problem as I was getting the code ready for you. I had copied the function from another one and the return statement was
RETURN FLOAT AS
When there were two decimal points in the value it was not a float ....
I changed it to RETURN VARCHAR(13) AS
and it worked.
Thanks for you help
RETURN FLOAT AS
When there were two decimal points in the value it was not a float ....
I changed it to RETURN VARCHAR(13) AS
and it worked.
Thanks for you help