lets say that I want to know all of the common factors with two numbers, 20 and 40, and write a script that will print them to the screen. I know I can use MOD to divide so I could write out a division of every number out of both 20 and 40 and then check if there are duplicates and print them, but that would take a lot of lines, is there a quicker way?
Asked
Active
Viewed 691 times
1 Answers
1
If you have a numbers table in your database (always a good tool to have) then you can easily do this with the T-SQL below:
declare @num1 int = 20;
declare @num2 int = 40;
select n.num as commonFactor
from dbo.Nums as n
where (n.num < case when @num1 > @num2 then @num1 else @num2 end)
and @num1 % n.num = 0
and @num2 % n.num = 0
If you don't have a numbers table then it is easy to create one - take a look at SQL, Auxiliary table of numbers for a few examples
Community
- 1
- 1
Paul McLoughlin
- 2,283
- 1
- 15
- 15
-
awesome thanks! do you know how the modulo (%) operator works? someone was saying that would be easy but did not explain how – user3570765 Apr 25 '14 at 05:13
-
The modulo operator is explained at http://technet.microsoft.com/en-us/library/ms190279.aspx – Paul McLoughlin Apr 25 '14 at 16:57