Can I make a SQL Server 2005 Table Column accept only letters and numbers ?

Question

When we have a column that will store a username that will only accept letters and numbers we always do validation on this input field using javascript or even server validation from code .. but i want to know if is there any way that can allow me make this validation on the Table column itself even some one try to enter data from any place it don't accept and throw exception ?

Answer 1

You could use a trigger on the target table and validate the input before the insert is performed.

Read more about triggers

Answer 2

You could probably do something with a check constraint on the column. T-SQL doesn't natively have a regex function, although LIKE (as mentioned by AakashM below) is smart enough to do this. To get a full regex capability you can use CLR integration to wrap the .Net Regex function and then wrap the regex with a UDF and use the check constraint to invoke the function to evaluate the contents of the column.

Here's another article about using CLR UDFs for regex matching in T-SQL.

Answer 3

Beaten to the punch by AakashM while I was writing this, but here's the code anyways:

CREATE TABLE #Table
(
    Data VARCHAR(50) NOT NULL  CHECK(Data NOT LIKE '%[^a-zA-Z90-9]%')
)

INSERT INTO #Table(Data)
VALUES ('123abc')

GO

INSERT INTO #Table(Data)
VALUES ('123_abc')

GO

SELECT * FROM #Table

DROP TABLE #Table

Note that the expression is a double negative, so "Not like not (a-z or 0-9)" so the % symbols will catch violations anywhere in the inserted string.