Solved

Checking for alpha characters in a field

Posted on 2008-10-01
7
237 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 250 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 250 total points
ID: 22617590

SELECT column, ...
FROM ...
WHERE column NOT LIKE '%[^a-z]%'
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

713 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