Query Syntax for Finding Field Values Containing NON-AlphaNumeric Characters

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

LVL 2
Chi Is CurrentAsked:
Who is Participating?
 
kerwinsiyConnect With a Mentor Commented:
try this.
SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE 
 isnumeric([Stock_Num]) = 0

Open in new window

0
 
kerwinsiyCommented:
select ISNUMERIC('12K')
SELECT Stock_Info.Stock_Num
FROM Stock_Info
WHERE 
 isnumeric([Stock_Num]) = 0

Open in new window

0
 
Chi Is CurrentAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
kerwinsiyCommented:
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
 
Chi Is CurrentAuthor Commented:
Values containing hyphens also appear in the result w/ statement above.

J
0
 
kerwinsiyCommented:
ill provide some values
tag it if it should be counted. thanks


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

0
 
Chi Is CurrentAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.