Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: Validation rule.. for numbers only

    I am getting some data from another database and only want to copy sensible data...

    How do I write a validation rule in SQL (SQL SERVER 2000) for a fax number, so that it only contains numbers-- i.e digits 0-9


    Thanks in advance

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    @Value NOT LIKE '%[^0-9]%'

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why the ^?
    I've never seen that used before.
    Also, Peso's method will check the existence of a single number in any given value. Whereas
    Code:
    WHERE value LIKE '[0-9][0-9][0-9][0-9]'
    Will only return 4 digit numeric results.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Peso's method will work, as long as you truly want only digits and no formatting characters.

    -PatP

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by georgev
    Why the ^?
    I've never seen that used before.
    Also, Peso's method will check the existence of a single number in any given value. Whereas
    Code:
    WHERE value LIKE '[0-9][0-9][0-9][0-9]'
    Will only return 4 digit numeric results.
    Look a little closer... The "^" is a prefix for "does not match"...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    My approach is more versatile, because it allows for all length of fax number, as long as there are only digits.

    Your approach needs to be edited every time you discover that people has different length of fax numbers in different countries, for example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •