-1

I want to add a regex to a SQL Server check constraint to input only characters not any number even not include any number in character. Actually I want to input only [a-zA-Z] without mixing or including any numbers.

Please help anybody to provide that regex.

Martijn Pieters
  • 963,270
  • 265
  • 3,804
  • 3,187
Hefaj Uddin
  • 1

1 Answers1

1

You can add a CHECK constraint that verifies there are no non-alphabetic characters using:

CHECK (col NOT LIKE '%[^a-zA-Z]%')

This constraint will fail for any value that has a character in it that is not a letter.

Demo on dbfiddle

Note that your column/table/database collation may be case-insensitive (e.g. latin1_swedish_ci) in which case you can get away with

CHECK (col NOT LIKE '%[^A-Z]%')
Nick
  • 123,192
  • 20
  • 49
  • 81
  • 1
    Side note for future visitors too; the collation affects whether like is case sensitive. Most SQL server default collations could get away with just `[^A-Z]` to match a-z too – Caius Jard Feb 15 '20 at 07:55
  • @CaiusJard good point. I'll add a note about that in the answer. – Nick Feb 15 '20 at 07:56
  • 1
    @HefajUddin You should accept the answer see how at - https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Suraj Kumar Feb 15 '20 at 10:17