Solved

column can only enter letters of the alphabet

Posted on 2010-08-13
5
502 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now