• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Find fields that contain non-alpha characters

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
MDWinter
Asked:
MDWinter
  • 3
  • 2
  • 2
1 Solution
 
theGhost_k8Database ConsultantCommented:
not regexp '[0-9a-z]';
0
 
theGhost_k8Database ConsultantCommented:
SELECT sr_code, sr_name
FROM source
WHERE FIELD_NAME not regexp '[0-9a-z]'
order by sr_code asc;
0
 
Gibu GeorgeChief Technology OfficerCommented:
WHERE sr_code REGEXP '[A-Z][a-z]'
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Gibu GeorgeChief Technology OfficerCommented:
sorry mine is wrong I added for the one with alpha
0
 
MDWinterAuthor Commented:
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
 
theGhost_k8Database ConsultantCommented:
Please try this: not regexp '^[0-9a-z]+$';
0
 
MDWinterAuthor Commented:
You are a gentleman sir!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now