By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Concatenate float data type

P: n/a
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!
Oct 16 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Oct 16 '08 #2

P: n/a
On Oct 16, 10:12*am, Plamen Ratchev <Pla...@SQLStudio.comwrote:
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 Ratchevhttp://www.SQLStudio.com
Thank you, that worked famously.
Oct 16 '08 #3

P: n/a
>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.
Oct 17 '08 #4

P: n/a
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
Oct 21 '08 #5

P: n/a
>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.
Oct 22 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.