SQLTeam.com | Weblogs | Forums

Count sequence of char

tsql

#1

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


#2

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)


#3

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.%'

#4

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


#5

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


#6

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)