Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

column can only enter letters of the alphabet

Posted on 2010-08-13
5
505 Views
Last Modified: 2012-08-13
Hi experts,
as I can validate that a column can only enter letters of the alphabet
0
Comment
Question by:enrique_aeo
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33434496
You need to add a CONTRAINT on that column something like this:
ALTER TABLE dbo.YourTableName ADD CONSTRAINT AlphaOnly CHECK (PATINDEX('%[^A-Z]%', YourColumnName) > 0)
0
 

Author Comment

by:enrique_aeo
ID: 33434542
HI
create table datosPersonales (nombre varchar(100))
go
ALTER TABLE datosPersonales
      ADD CONSTRAINT nombre CHECK (PATINDEX('%[^A-Z]%', nombre) > 0)
      
insert into datosPersonales values ('ABCD')
i have this error
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "nombre". The conflict occurred in database "AdventureWorks", table "dbo.datosPersonales", column 'nombre'.
The statement has been terminated.

0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 50 total points
ID: 33434554
Use WITH NOCHECK
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 33434660
It means you already have data that conflicts with your own rules.  If you do not care about existing data than use WITH NOCHECK.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 33434704
Oops I am sorry it looks like I mislead you the check constraint should have read:
ALTER TABLE dbo.YourTableName ADD CONSTRAINT AlphaOnly CHECK (PATINDEX('%[^A-Z]%', YourColumnName) = 0)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question