Solved

column can only enter letters of the alphabet

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

16 Experts available now in Live!

Get 1:1 Help Now