?
Solved

Checking for alpha characters in a field

Posted on 2008-10-01
7
Medium Priority
?
247 Views
Last Modified: 2010-04-21
Hello All

Is it possible to select items in a SQL field that can only contain letters and nothing else, no numbers, special characters etc.

Many thanks
0
Comment
Question by:bostonste
[X]
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
  • 3
  • 3
7 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 22617237
Yup...

This simple example shows how to use patindex to do it.


select 1
where patindex('%[^A-Z]%','aABaC')=0


select 1
where patindex('%[^A-Z]%','aAB1C')=0
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22617243
just repalce 'aAB1C' or 'aABaC' with your column name.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 22617590

SELECT column, ...
FROM ...
WHERE column NOT LIKE '%[^a-z]%'
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22617626
Yes, I know that's annoying because of the NOT of a not (^), but really, there's no other easy way to do it :-) , at least AFAIK .
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22617634
That does the same thing.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22617809
Except that SQL would consider an index scan on that column for my code, since the column is not imbedded within a function call :-) .
0
 

Author Closing Comment

by:bostonste
ID: 31502089
EXCELLENT CHAPS
MANY THANKS
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

770 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