0

I have a table named CustomerAddr. The table is for a user to enter customer's address information. One column named StNumber should only allow numeric characters ex. 0-9 just like a phone number, however the user entering the customers address information can put any characters; such as alpha, special characters etc.

I want the column StNumber to ONLY accept numeric values 0-9 four characters max. If the user should try to enter anything but a number in the customers address information screen it should be blocked. I changed the data type for StNumber to numeric(5) but the user still can enter any character. Help would be great.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tiburon1
  • 29
  • 1
  • 8
  • You need to make that validation on the UI at the time of user inputs values if it's is web application look at this http://www.htmlgoodies.com/html5/tutorials/whats-new-in-html5-forms-handling-numeric-inputs-using-the-number-and-range-input-types.html#fbid=_HB79Wl8Ql_ – Mahesh Jan 16 '15 at 05:37
  • 1
    `Numeric(5)` will never allow `character` something else wrong – Pரதீப் Jan 16 '15 at 05:38
  • As @Coder of Code says above, you should validate the data at the time of entry, not rely on the database to check it. The database should also use correct datatypes and constraints to "protect" itself and ensure that it only ever stores valid data. The best designed UIs don't allow the user to make mistakes, so would prevent entry of anything besides digits or dash. Either the field shouldn't accept the invalid keystroke, or the "Save" button should be disabled until all fields are valid. However you do it, the data shouldn't be sent to the database unless the program thinks it is valid. – Turophile Jan 16 '15 at 05:52

2 Answers2

0
ALTER TABLE CustomerAddr
ADD CONSTRAINT chk_num CHECK (ISNUMERIC ( StNumber ))
sumit
  • 15,003
  • 12
  • 69
  • 110
  • I think OP is asking about UI validation `If the user should try to enter anything but a number in the customers address information screen it should be blocked` – Mahesh Jan 16 '15 at 05:37
  • 2
    @timus2001 - FYI `Isnumeric` has too many drawbacks. It will allow string like `'$1000', '1e1' ` – Pரதீப் Jan 16 '15 at 05:49
  • 1
    @NoDisplayName - at least your examples contain *some* digits. I prefer the even less sane examples like `'£'` or `','` – Damien_The_Unbeliever Jan 16 '15 at 07:40
0

As all said, you are talking about UI validation. You should think about using regular expressions. For example, you can use the regular expression ^[0-9]*$ to make sure only numerals are typed. For your case, you can use ^[0-9][0-9][0-9][0-9]$ to accept only four digits.

Depending on the language you use, you need to know how to make this validation. For example, the below link gives an example in .NET:

Regex that accepts only numbers (0-9) and NO characters

Community
  • 1
  • 1
HGF
  • 389
  • 3
  • 15
  • Can I alter the table in a query to allow only numbers? – tiburon1 Jan 17 '15 at 05:37
  • Sure, it depends on the DBMS you're using. For example: You can set the column data type to `smallint` in SQL Server and add a constraint to make sure it lies in a certain range to make sure no negative numbers are inserted. – HGF Jan 17 '15 at 07:35
  • Example for adding a constraint on column in SQL Server: `ALTER TABLE ADD CONSTRAINT CHECK ( >= 0 AND <= 9999)` – HGF Jan 17 '15 at 07:57