?
Solved

Find fields that contain non-alpha characters

Posted on 2009-04-21
7
Medium Priority
?
254 Views
Last Modified: 2012-05-06
We have a table within a database that stores codes for our different advertisers.

We used to allow our users to update themselves, but they kept using non-alpha characters in the code.

I now want to tidy up the table by deleting/fixing the fields which contain non alphanumeric characters.

What's the correct query to find them? Can you replace the ??? in my code below:
SELECT sr_code, sr_name 
FROM source 
WHERE ??? 
order by sr_code asc;

Open in new window

0
Comment
Question by:MDWinter
  • 3
  • 2
  • 2
7 Comments
 
LVL 21

Expert Comment

by:K V
ID: 24195153
not regexp '[0-9a-z]';
0
 
LVL 21

Expert Comment

by:K V
ID: 24195171
SELECT sr_code, sr_name
FROM source
WHERE FIELD_NAME not regexp '[0-9a-z]'
order by sr_code asc;
0
 
LVL 12

Expert Comment

by:Gibu George
ID: 24195186
WHERE sr_code REGEXP '[A-Z][a-z]'
0
Independent Software Vendors: 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 12

Expert Comment

by:Gibu George
ID: 24195201
sorry mine is wrong I added for the one with alpha
0
 

Author Comment

by:MDWinter
ID: 24195241
Think I might have been unclear. I used the code that theGhost suggested (attached for clarity) but got zero results.

I want any field that CONTAINS a non alpha character. Not any field that contains ONLY non-alphas.

I know for a fact that one of the records has a value of T@ set for sr_code. This should be found by my query...
SELECT sr_code, sr_name
FROM source
WHERE sr_code not regexp '[0-9a-z]'
order by sr_code asc

Open in new window

0
 
LVL 21

Accepted Solution

by:
K V earned 2000 total points
ID: 24195282
Please try this: not regexp '^[0-9a-z]+$';
0
 

Author Closing Comment

by:MDWinter
ID: 31572791
You are a gentleman sir!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

621 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