Counting zero's in decimal field

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

Counting zero's in decimal field

Author

Topic

Axxib
Starting Member

6 Posts

Posted - 2008-08-19 : 11:31:26

Hi All

Can someone please help: I need to be able to count the number of énding zero's in a decimal field (before the decimal point), for example if field 'Amount' has the values 10000.11, 11000.11, 20000.11 and 22000.11 I need to be able to specify all values where the number of ending zero's is 4 and get the result 10000.11 and 20000.11.

Is there a way in sql that this can be acheived?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 11:50:32

[code]DECLARE @Sample TABLE
(
Data DECIMAL(10, 2)
)

INSERT @Sample
SELECT 30300.10 UNION ALL
SELECT 10000.11 UNION ALL
SELECT 11000.11 UNION ALL
SELECT 20000.11 UNION ALL
SELECT 90000 UNION ALL
SELECT 50000.00 UNION ALL
SELECT 22000.11

SELECT Data
FROM @Sample
WHERE Data LIKE '%' + REPLICATE('0', 4) + '.%'[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 11:51:33

SELECT Amount FROM YourTable WHERE SUBSTRING(REVERSE(CAST(Amount AS varchar(50))),CHARINDEX('.',REVERSE(CAST(Amount AS varchar(50))))+1,@number)=REPLICATE('0',@number)

where @number is number of zeroes you want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 11:55:07

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Data DECIMAL(10, 2)
	)

INSERT	@Sample
SELECT	30300.10 UNION ALL
SELECT	10000.11 UNION ALL
SELECT	11000.11 UNION ALL
SELECT	20000.11 UNION ALL
SELECT	90000    UNION ALL
SELECT	50000.00 UNION ALL
SELECT	22000.11

SELECT	Data
FROM	@Sample
WHERE	Data LIKE '%' + REPLICATE('0', 4) + '.%'

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

Wont this return all values that have n or more ending zeroes where n is what we are looking for?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-19 : 11:58:12

Maybe this?

DECLARE @Yak TABLE(Val DECIMAL(18,8))
DECLARE @DigitCount INT

SET @DigitCount = 4

INSERT @Yak
SELECT 10000.11
UNION ALL SELECT 11000.11
UNION ALL SELECT 20000.11 
UNION ALL SELECT 22000.11 

SELECT Val
FROM @Yak
WHERE CAST(Val AS INT) % POWER(10, @DigitCount) = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 11:58:41

quote:
Originally posted by visakh16

Wont this return all values that have n or more ending zeroes where n is what we are looking for?

You are right. So does your example.

DECLARE	@Sample TABLE
	(
		Data DECIMAL(10, 2)
	)

INSERT	@Sample
SELECT	30300.10 UNION ALL
SELECT	10000.11 UNION ALL
SELECT	11000.11 UNION ALL
SELECT	20000.11 UNION ALL
SELECT	10.12    UNION ALL
SELECT	100000.1 UNION ALL
SELECT	90000    UNION ALL
SELECT	50000.00 UNION ALL
SELECT	22000.11

-- Peso
SELECT	Data
FROM	@Sample
WHERE	Data LIKE '%[^0]' + REPLICATE('0', 4) + '.%'

--Lamprey
SELECT	Data
FROM	@Sample
WHERE	CAST(Data AS INT) % POWER(10, 4) = 0

-- Visakh
SELECT	Data
FROM	@Sample
WHERE	SUBSTRING(REVERSE(CAST(Data AS VARCHAR(50))), CHARINDEX('.', REVERSE(CAST(Data AS VARCHAR(50)))) + 1, 4) = REPLICATE('0', 4)

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-19 : 13:54:37

One more way:

declare @sample table (data decimal(30, 2) )

insert	@sample
select	4444433303030000000.10 union all
select	4444433303030030000.10 union all
select	30303.10 union all
select	30300.10 union all
select	10000.11 union all
select	11000.11 union all
select	20000.11 union all
select	10.12    union all
select	100000.1 union all
select	90000    union all
select	50000.00 union all
select	22000.11

print 'Show trailing zero count'
select
	Data,
	Trailing_Zeros =
		len(floor(Data)) - len(convert(bigint,reverse(floor(Data))))
from
	@sample


print 'Select where trailing zero count = 4'
select
	Data
from
	@sample
where
	4 = len(floor(Data)) - len(convert(bigint,reverse(floor(Data))))


Results:

Show trailing zero count
Data                             Trailing_Zeros 
-------------------------------- -------------- 
4444433303030000000.10           7
4444433303030030000.10           4
30303.10                         0
30300.10                         2
10000.11                         4
11000.11                         3
20000.11                         4
10.12                            1
100000.10                        5
90000.00                         4
50000.00                         4
22000.11                         3

(12 row(s) affected)

Select where trailing zero count = 4
Data                             
-------------------------------- 
4444433303030030000.10
10000.11
20000.11
90000.00
50000.00

(5 row(s) affected)

CODO ERGO SUM

Axxib
Starting Member

6 Posts

Posted - 2008-08-19 : 15:35:02

Thanks for all your help guys!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 03:53:53

Another way similar to MVJ's method


select data from
(
	select data,cast(data as int) as data_int
	from @sample
) as t
where len(data_int)-len(replace(data_int,'0','')) =4

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 05:33:57

Madhi, what happens to the value "100100.11"?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 06:28:53

And this is not a sample data provided by OP, but what happens to values less than zero?

DECLARE	@Sample TABLE
	(
		Data DECIMAL(10, 2)
	)

INSERT	@Sample
SELECT	30300.10 UNION ALL
SELECT	10000.11 UNION ALL
SELECT	11000.11 UNION ALL
SELECT	20000.11 UNION ALL
SELECT	10.12    UNION ALL
SELECT	100000.1 UNION ALL
SELECT	90000    UNION ALL
SELECT	50000.00 UNION ALL
SELECT	300300.0 UNION ALL
SELECT	-70000.0 UNION ALL
SELECT	22000.11

-- Peso
SELECT	Data
FROM	@Sample
WHERE	Data LIKE '%[^0]' + REPLICATE('0', 4) + '.%'

--Lamprey
SELECT	Data
FROM	@Sample
WHERE	CAST(Data AS INT) % POWER(10, 4) = 0

-- Visakh
SELECT	Data
FROM	@Sample
WHERE	SUBSTRING(REVERSE(CAST(Data AS VARCHAR(50))), CHARINDEX('.', REVERSE(CAST(Data AS VARCHAR(50)))) + 1, 4) = REPLICATE('0', 4)

-- MVJ
SELECT	Data
FROM	@Sample
WHERE	4 = LEN(FLOOR(Data)) - LEN(CONVERT(BIGINT, REVERSE(FLOOR(Data))))

-- Madhi
SELECT	Data
FROM	(
		SELECT	Data,
			CAST(Data AS INT) AS Data_INT
		FROM	@Sample
	) AS t
WHERE	LEN(Data_INT) - LEN(REPLACE(Data_INT, '0', '')) = 4

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 07:27:48

quote:
Originally posted by Peso

Madhi, what happens to the value "100100.11"?

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

Then my where condition would be

where data_int/10000.0 not like '%0.%' and right(data_int/10000.0,6)=0
and data_int/10000.0 like '%.0%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 09:51:27

Oh! Multiple WHERE clause...

Your second suggestion is about 3 times faster than my first suggestion. Very good!
But my second suggestion is about 4 times faster than your second suggestion.

Here is my second suggestion, which is about 12 times faster than my first suggestion

SELECT	Data
FROM	@Sample
WHERE	CAST(Data AS INT) % 10000 = 0
	AND (CAST(Data AS INT) / 10000) % 10 > 0

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 10:28:49

The reason I did it by computing the number of trailing zeros was that I thought it was more flexible. I thought there was a possibility that the OP had not stated the requirements exactly.

-- as specified
where 4 = len(floor(Data)) - len(convert(bigint,reverse(floor(Data))))

-- 4 or more
where 4 <= len(floor(Data)) - len(convert(bigint,reverse(floor(Data))))

-- 1 or 6
where 4 <= len(floor(Data)) - len(convert(bigint,reverse(floor(Data)))) in (1,6)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 02:54:47

quote:
Originally posted by Peso

Oh! Multiple WHERE clause...

Your second suggestion is about 3 times faster than my first suggestion. Very good!
But my second suggestion is about 4 times faster than your second suggestion.

Here is my second suggestion, which is about 12 times faster than my first suggestion
SELECT Data
FROM @Sample
WHERE CAST(Data AS INT) % 10000 = 0
AND (CAST(Data AS INT) / 10000) % 10 > 0

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

This eliminates negative numbers

Second condition should be

(CAST(Data AS INT) / 10000) % 10 <> 0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources