Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Checking for alpha characters in a field

Posted on 2008-10-01
7
Medium Priority
?
252 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 70

Assisted Solution

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

SELECT column, ...
FROM ...
WHERE column NOT LIKE '%[^a-z]%'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 70

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 70

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 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