Record Length

Question

Record Length

gboyer

Old Hand

Points: 310
More actions
August 31, 2009 at 11:59 am

#210146

All,
I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

gboyer (8/31/2009)
All,
I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer

You could use the modulo operator, you first need to multiply your float by whichever number of decimals you want to check, cast as integer, and check the last digit with modulo 10.

Here's how:

DECLARE @f FLOAT

SET @f = 25.300

SELECT count(@f)

WHERE cast((@f * 1000 /* that is for 2 decimals*/) AS INT)%10 = 0

SET @f = 25.301

SELECT count(@f)

WHERE cast((@f * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0

Tell me if that helps,

Cheers,

J-F

Lowell SSC Guru Points: 323518 More actions · Answer 2

gboyer (8/31/2009)
All,
I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer

if you convert a float to a string, there is always going to be more than three chars after the decimal point.

check out the results of my example table, you get values like 151.000000000000000000000 for the conversion when it rounds nice an pretty....so it wouldn't help to convert to a string.

i think the thing to do is to forget casting to string,a nd jsut find items that are not equal to the round to two decimal palces of the number....that would tell you that 26.92 26.920000076293945

but that 151.00 = 151.000000000000000000000

create table #Example(exampleId int identity(1,1), ExampleTotal float)

insert into #Example

select 150.0 + (1.0 / RW) from (

select top 30 row_number() over (order by id) as RW,id from sysobjects

) x

--results

/*

151.000000000000000000000

150.500000000000000000000

150.333333333333333333333

150.250000000000000000000

150.200000000000000000000

150.166666666666666666666

150.142857142857142857142

150.125000000000000000000

150.111111111111111111111

*/

select * from #Example where round(ExampleTotal,2) ExampleTotal --two decimal places

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gboyer Old Hand Points: 310 More actions · Answer 3

Here is an example of my results with my T-Sql:

SELECT CHARINDEX('4', Amount, 1) AS Length, Amount, EnrollmentYear, CHARINDEX('4', SSN, 1) AS Expr1, CAST(Amount AS varchar(50))

AS CastExample

FROM dbo.[Benefits.Benefits]

525.342009025.34

026.922009126.92

00200910

00200900

00.9200930.9

39.43200969.43

026.922009026.92

00.9200960.9

00200910

0180.3720096180.37

00.9200900.9

09.5799999237060547200989.58

022.522009622.52

00200900

0126.0599975585937520090126.06

00200900

010.9099998474121092009810.91

00200960

026.9200000762939452009826.92

02.809999942779541200982.81

00200900

05.61200905.61

07.73200917.73

026.922009026.92

02.7000000476837158200982.7

00200930

33.42200933.42

0252009325

00200930

011.7700004577636722009811.77

0126.0620093126.06

016.892009316.89

02.81200902.81

00200900

019.52009119.5

017.7700004577636722009017.77

0802009680

06.57200906.57

00200910

0852009085

39.4300003051757812200909.43

00200900

00200930

00200980

016.892009016.89

00.9200980.9

013.582009313.58

00200910

022.5200004577636722009022.52

00200980

224.552009024.55

00200960

00200930

1402009040

00200900

00200980

NULLNULLNULLNULLNULL

Sorry for the mess.

I tried to do a cast to varchar and it rounded the result

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

Have you even read the solutions we posted to your problem, before posting that data?

Both solutions will solve the problem you have actually with the float values. If you have problems understanding the way it works, please ask questions.

Cheers,

J-F

gboyer Old Hand Points: 310 More actions · Answer 5

You're right. I am sorry. I have a tough admitting I do not understand something. With your solution, J-F Bergeron, I have no idea how to adapt it to my situation. And with not understanding I have no idea even where to begin to ask questions.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Well, if that is the case, I will explain to you how you could implement it.

You said you wanted to count the number of occurences that had more than 2 decimals.

Here's how:

First Method : The modulo method.

SELECT count(* )

FROM YourTableName

WHERE cast((FloatColumnName * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0

Second Method, compare the rounding of the value with the value itself, to see if there was any more decimals

SELECT count(* )

FROM YourTableName

WHERE round(FloatColumnName,2) FloatColumnName

Just put in your table name, and your column name, and it will give you the count you were expecting.

If you need more assistance, please feel free to post your question.

Cheers,

J-F

gboyer Old Hand Points: 310 More actions · Answer 7

J-F,

Thanks for the push. I went and looked again at the post from Lowell and with you help was able to move forward. Thanks again. Sorry lack of confidence is showing.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 8

Happy it helped,

and don't worry, we all have to start somewhere, just ask questions if you are not sure. But playing with the supplied code is always a good way to learn.

Have a nice day,

Cheers,

J-F

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 9

gboyer,

Since you're brand new to the forum, you might want to take a look at the article at the following URL...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you follow those steps (yep... it takes just a bit of time extra, but not much), I guarantee that you'll get better answers quicker for your future posts even if you're not 100% sure which question to ask.

And, welcome aboard... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)