?
Solved

Query Syntax for Finding Field Values Containing NON-AlphaNumeric Characters

Posted on 2008-11-03
7
Medium Priority
?
511 Views
Last Modified: 2010-05-18
Hello ~

Could someone suggest a way to query a field for all field values containing non-alpha-numeric characters.  I've written the following to find all instances of "#", but I also need to find all other characters INCLUDING punctuation:

Best Regards, Jacob
SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE (((InStr(1,[Stock_Num],"#"))>0));

Open in new window

0
Comment
Question by:Chi Is Current
  • 4
  • 3
7 Comments
 
LVL 2

Expert Comment

by:kerwinsiy
ID: 22874189
select ISNUMERIC('12K')
SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE 
 isnumeric([Stock_Num]) = 0

Open in new window

0
 
LVL 2

Accepted Solution

by:
kerwinsiy earned 1500 total points
ID: 22874194
try this.
SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE 
 isnumeric([Stock_Num]) = 0

Open in new window

0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 22874238
kerwinsiy ~  Thank you for your reply.

I would also like to include hyphens as acceptable characters.  In other words,

show: all field values containing non-alphanumeric characters, EXCLUDING hyphens.

I have also found the following LIKE statement also works but also displays values containing hyphens (of course):

SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE (((Stock_Info.Stock_Num) Like "*[!0-9A-Z]*"));

Open in new window

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 2

Expert Comment

by:kerwinsiy
ID: 22874248
just replace the hypen to blank in order to exlude it. hope it help

SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE
 isnumeric(REPLACE([Stock_Num],'-','')) = 0

0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 22874263
Values containing hyphens also appear in the result w/ statement above.

J
0
 
LVL 2

Expert Comment

by:kerwinsiy
ID: 22874287
ill provide some values
tag it if it should be counted. thanks


1) a
2) a2
3) 34
4) 43-23

0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 22874290
Searching the web also, I ran across the following with an excellent discussion on: LIKE

http://bytes.com/forum/thread448043.html

I'm using:

SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE (((Stock_Info.Stock_Num) Like "*[!-!0-9A-Z]*"));


Which works perfectly.  I can also include / exclude other characters easily.

Thank you for your assistance here.

Best Regards, Jacob
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 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