Count sequence of char

noam_graizer · March 17, 2016, 2:21pm

I have a table with very long float numbers, like this one.
0.000000000000002811457254345520763198945583010320016233492735204531033973922240339918522302587039593

i need to count how many zero i have between the float till the first digits.
any idea ?

thanks

djj55 · March 17, 2016, 2:34pm

Note I get the error:

Msg 1007, Level 15, State 1, Line 1
The number '0.000000000000002811457254345520763198945583010320016233492735204531033973922240339918522302587039593' is out of the range for numeric representation (maximum precision 38).

You might try something like
CAST(RIGHT(CAST(@f AS VARCHAR(100)),3) AS INT)

Kristen · March 17, 2016, 3:03pm

Assuming its a string then

SELECT PATINDEX('%[^0.]%', '0.000000000000002811457254345520763198945583010320016233492735204531033973922240339918522302587039593')

will return 17. Subtract 3 to get the number of zeros after the decimal place

Add a WHERE or CASE WHEN to ensure that the value starts with "0."

MyValue LIKE '0.%'

JeffModen · March 18, 2016, 2:09pm

I'm just curious... why does this need to be done? What is the end goal here?

Kristen · March 18, 2016, 3:05pm

Good point! Perhaps convert to FLOAT and see what the negative-number-bit is after the "E" exponent?

JeffModen · April 5, 2016, 4:17am

I guess another question that I would have is (and sorry if I missed it somewhere), what is the datatype of that string? It's certainly to long to be an actual FLOAT and, at 100 characters (not including the decimal point), it's also too long even for a DECIMAL(38,38)