sql, incrementing numbers 2

[747576]

Hi,

Is there a mathematical function or code in sql that will work out if a value is a multiple of 3. In my database a value entered into a column in my table can only increment by 3, so 3, 6, 9, 12.... is ok, 1,2,4,5,7....not ok

is there way of checking a value using a function.

Thank you

 

[gmmastros]

You can use the MOD operator. Technically, this returns the remainder from integer division. Ex: 7 divide by 3 = 2 and 1 / 3. Mod will return the 1 in this case.

In SQL Server, the MOD operator is represented by a percent symbol. Ex:

Select 0 % 3,
       1 % 3, 
       2 % 3, 
       3 % 3,
       4 % 3,
       5 % 3,
       6 % 3,
       7 % 3

so.... if YourNumber % 3 = 0 then it is a multiple of 3.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fxthoth]

You can also write constraint:

create table example(
	col1 int
)

alter table example
	add constraint mod3
	check (col1%3=0)

insert into example values (0)--is ok
insert into example values (1)--fails
insert into example values (3)--is ok
insert into example values (4)--fails