length of a number (number of digits)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

length of a number (number of digits)

Author

Topic

carlospro7
Starting Member

5 Posts

Posted - 2008-07-01 : 01:34:35

I'm trying to get the length of a number (the number of characters, or the number of digits plus the 1 period in a float number). The field that I'm using is of floats and when I use


SELECT field1, LEN(field1) length

it returns the length but the most it goes is to 7. For example

field1      | length
----------------------
234.3       | 5
23          | 2
4333.1      | 6
44.31543    | 7           (this one should say 8)
1.00000002  | 7           (this one should say 10)
3333.123433 | 7           (this one should say 11)

When the number has more than 7 characters it doesn't count them.
I have also tried converting the floats to chars, but I get the same results. LEN(CONVERT(char(53),field)). I tried plugging different numbers in the char(), but nothing.

When I select whats in field1 I get the actual numbers, so I don't understand what's going on.

I also tried decimals instead of floats, but it includes the trailing zeros at the end depending on how many decimals i specify it to have. I don't want to use decimals though b/c the data in field1 may need to have different decimals places.

Can someone explain whats going, and if there is an easier way to do this.

Thanks,
los

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 02:44:51

what is the data type of your column "field1" ?

KH
[spoiler]Time is always against us[/spoiler]

carlospro7
Starting Member

5 Posts

Posted - 2008-07-01 : 06:17:06

data type is float

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-01 : 06:27:53

Use DATALENGTH instead of LEN.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-01 : 06:29:42

Also see this topic were LEN vs DATALENGTH is discussed about binary representation.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105415

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-01 : 06:33:59

And here is some information about how a FLOAT is stored in memory.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849

E 12°55'05.25"
N 56°04'39.16"

carlospro7
Starting Member

5 Posts

Posted - 2008-07-01 : 09:53:53

DATALENGTH returns 8 for every column regardless of how many digits field1 has.

I don't think any data is lost since when I select all rows in field1 (floats) the actual numbers are displayed. So if I insert the number 3333.123433 (which has 10 digits + period, length of 11) in field1, "3333.123433" is displayed after a selection and not "3333.12" which has a length of 7.


SELECT field1 ...

field1      
------------
44.31543    
1.00000002 
3333.123433

So when I call LEN(field1), I know that the 11 chars in "3333.123433" are being sent to LEN. My guess is that, LEN does something that it looses information, or something like that.

I don't know though, I normally work with C++ and not SQL. So I don't entirely understand how this works.

Thanks for the suggestions though Peso. I'll see if that helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 10:08:48

the len() behave such way because function len() takes in a string expression. Using len() on the float implicitly convert the value to string before determine the length.
see BOL on len() http://msdn.microsoft.com/en-us/library/ms190329.aspx?n=0

DECLARE @TABLE TABLE
(
	field	float
)

INSERT INTO @TABLE (field)
SELECT	234.3       	UNION ALL
SELECT	23          	UNION ALL
SELECT	4333.1      	UNION ALL
SELECT	44.31543    	UNION ALL
SELECT	1.00000002  	UNION ALL
SELECT	3333.123433

SELECT	field, LEN(field), CONVERT(varchar(20), field)
FROM	@TABLE

/*
field                                                                                  
------------------- ----------- -------------------- 
 234.30000000000001           5 234.3
               23.0           2 23
 4333.1000000000004           6 4333.1
 44.315429999999999           7 44.3154
 1.0000000200000001           1 1
 3333.1234330000002           7 3333.12

(6 row(s) affected)
*/

Since your field data type is float, float is only an approximate number. Take a look at above script. I store 4333.1 into the float field and when select back it gives 4333.1000000000004.

Why do you need to know the "length" of the data field ?

KH
[spoiler]Time is always against us[/spoiler]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-01 : 11:57:37

try this, but not the proper way

DECLARE @Table TABLE (field1 FLOAT)

INSERT INTO @Table
SELECT 234.3 UNION ALL
SELECT 23 UNION ALL
SELECT 4333.1 UNION ALL
SELECT 44.31543 UNION ALL
SELECT 1.00000002 UNION ALL
SELECT 3333.123433

SELECT field1
, REPLACE(LEFT(CAST(LTRIM(RTRIM(STR(ROUND(field1,10),50,10))) AS VARCHAR(40)), LEN(CAST(LTRIM(RTRIM(STR(ROUND(field1,10),50,10))) AS VARCHAR(40))) + 1 - PATINDEX('%[^0]%', REVERSE(CAST(LTRIM(RTRIM(STR(ROUND(field1,10),50,10))) AS VARCHAR(40))))), '.', '')
FROM @Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources