CODEWITHSUNDEEP
sqlsql-server
user38858
user38858

Reputation: 316

Truncate and cast into varchar

I have a number I'm trying to cast into a varchar, and I don't care if it gets truncated.

My problem is that I keep getting these errors, depending on what I try:

String or binary data would be truncated.

Arithmetic overflow error for type varchar, value = 0.000052

Here is my current attempts:

declare @a as varchar(10), @b as varchar(10), @c as varchar(10)

set @a =cast(@b as float) / cast(@c as float)

DECLARE @temp TABLE
(
  [read] varchar(20), 
  [percent] varchar(10)
)
insert into @temp ([read],[percent])
  VALUES('text', @a)

I've also tried some of the following for the second line:

set @a = cast( cast(@b as float) / cast(@c as float) as varchar())

One oddity is that it works some of the time, but not all of the time. I've been able to discover that it only works when @a is large enough and not negative. When it is either negative or small, I get some of the above errors.

Upvotes: 2

Views: 2043

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

If you want to convert floating point numbers to characters strings, I highly recommend the str() function, which is documented here.

In your case, it would look something like:

set @a = str( cast(@b as float) / cast(@c as float), 10, 4)

where 10 and 4 are whatever scale and precision that you want.

By the way, when using varchar() in SQL, you should always include a length. Do not depend on the defaults, because they can change depending on where the expression is used.

Upvotes: 4

Related Questions