Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Checking for alpha characters in a field

Posted on 2008-10-01
7
Medium Priority
?
256 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

810 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