CREATE RULE

Question

CREATE RULE

JMI

SSChampion

Points: 11831
More actions
September 18, 2003 at 9:45 pm

#82258

I have this rule:
------------------
CREATE RULE org_name
AS
@value LIKE '[1-10]'
-------------------
When I insert '0' no errors
When I insert '2' I get error
I need to restrict to integer between 1 and 10

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

quote:
BOL 2000:
Examples
A. Rule with a range
This example creates a rule that restricts the range of integers inserted into the column(s) to which this rule is bound.
CREATE RULE range_rule AS
@range >= $1000 AND @range < $20000

Try

CREATE RULE range_rule AS

@range >= 1 AND @range <= 10

JMI SSChampion Points: 11831 More actions · Answer 2

I know I can do that.

But the whole point is

I want to learn how

to use pattern matchin

in my rules.

Something like:

@value LIKE '[1-10]__[1-100]'

But it doesn't work

So I started with [1-10]

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

quote:
I want to learn how to use pattern matchin in my rules.

I did'nt know that!

quote:
BOL 2000: Rules, a backward compatibility feature, perform some of the same functions as check constraints.

Create Rule Rule_Range as 

        (@Range Like '[1][0]' 

        Or @Range like '[1-9]') And

        DataLength(@Range)<=2

JMI SSChampion Points: 11831 More actions · Answer 4

[1-9] doesn't work

[1][0] doen't make sense

And how do I restrict

to any two characters?

_ _?

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 5

[1-9] doesn't work

BOL 2000:

[] = Any single character within the specified range ([a-f]) or set ([abcdef]).

Then the next become 'hardcoded', first character must be a 0 and the second must be 1

[1][0] doen't make sense

And how do I restrict to any two characters?

BOL 2000:

_ (underscore) Any single character.

Set NoCount On

Create Table Test(A Varchar(100))

Go

Insert Test

Select '10'

Insert Test

Select '9'

Insert Test

Select '8'

Insert Test

Select '7'

Insert Test

Select '6'

Insert Test

Select '0'

Insert Test

Select '1'

Go

Select '[0-9]',* from Test Where A Like '[0-9]'  -- Only single pos 0 to 9

Select '[1][0]',* from Test Where A Like '[1][0]' -- pos 1 must be a '1' and second pos must be '0'

Select '__',* from Test Where A Like '__' -- any single char in first and second position

Select '%',* from Test Where A Like '%' -- Any number of characters

Go

Drop Table Test

Go

Last open SQL Server 2000 Books Online and search for the word LIKE . There are more examples.

JMI SSChampion Points: 11831 More actions · Answer 6

I gave up.

I created a CONSTRAINT:

ALTER TABLE [dbo].[Organization] WITH NOCHECK ADD

CONSTRAINT [CK_Organization] CHECK (len([Name]) < 7

and (left([Name],2) = '03'

or (left([Name],2) = '02'

or left([Name],2) = '01'))

and (right([Name],2) >= 1 and right([Name],2) <= 101))

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

BOL 2000:

LEN

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailingblanks.

DATALENGTH

Returns the number of bytes used to represent any expression.