Concatenate float data type

I have a need to make a group of records 'unique' by concatenating a
foreign key value to another field, the two are float and varchar
type. Now, I'll have to cast the float column, but then I end up with
something like this:

'2.20824e+006 02316.PENDING00097840-CC'

The float value actually is: 2208239

How can I get a straight up number to text conversion of this value? I
tried Cast to varchar, that doesn't work.

Any help would be greatly appreciated, thank you!

re: Concatenate float data type

You can cast first to DECIMAL and then to character data type, or use
the STR function:

CREATE TABLE Foo(
keycol INT PRIMARY KEY,
datacol FLOAT);

INSERT INTO Foo VALUES(1, 2208239);

SELECT LTRIM(STR(datacol)),
CAST(CAST(datacol AS DECIMAL(10, 0)) AS VARCHAR(20))
FROM Foo

--
Plamen Ratchev
http://www.SQLStudio.com

re: Concatenate float data type

On Oct 16, 10:12*am, Plamen Ratchev <Pla...@SQLStudio.comwrote:Thank you, that worked famously.

re: Concatenate float data type

>I have a need to make a group of records [sic: rows are not records] 'unique' by concatenating a foreign key value to another field [sic], the two are float and varchar
type.<<

NO! Keys are discrete values by definition. FLOAT is an approximate
data type and cannot be a key. Please read any article on flaotign
point math and the IEEE Standards. When you test floats for equality,
there is this epsilon; a small value which matches two values when
they are "close enough" to each other. Since floats were only meant
to be used for computations, this works fine.

You need to use an exact numeric type or CHAR(n) for keys. Or decide
that you never want data integrity.

re: Concatenate float data type

I have a need to make a group of records 'unique' by concatenating a foreign key value to another field, <

Without having more details, it sounds to be a candidate for a
compound primary key.

CREATE TABLE Foo
(bar1 DECIMAL(10,5) NOT NULL, -- something instead of FLOAT
bar2 VARCHAR(30) NOT NULL
REFERENCES Foo2(bar2), -- the foreign key
PRIMARY KEY(bar1, bar2)); -- compound key, no duplicates

brgds

Philipp Post

re: Concatenate float data type

>Without having more details, it sounds to be a candidate for a compound primary key <<.

No! Think about it. They are an approximate computation numeric.
Entities are discrete by their nature. There is no such thing as a
approximate key. This is a problem with (longitude, latitude) pairs
for a location and why GIS uses a grid system of triangles instead.