sql query for specific conditions on field

access 2010
sql query for specific conditions

I have an sql query where i need the following additional criteria:
length of string
 no numerics

SELECT DISTINCT AGI_CROSS_REVIEW_2.MFG, Count(AGI_CROSS_REVIEW_2.MFG) AS CountOfMFG
FROM AGI_CROSS_REVIEW_2
GROUP BY AGI_CROSS_REVIEW_2.MFG
ORDER BY Count(AGI_CROSS_REVIEW_2.MFG) DESC;

Open in new window


The MFG field needs to be:
Length greater than 4
MFG field can not  contain any numeric in the data

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
"exclude records where a numeric value exists in MFG field"

Does this mean, no numbers, anywhere in the [MFG] field?  If so:

SELECT CR.MFG, Count(*) AS CountOfMFG
FROM AGI_CROSS_REVIEW_2 CR
WJERE Len(CR.MFG & "") > 4
AND NOT CR.MFG Like "*[0-9]*"
GROUP BY CR.MFG
ORDER BY Count(CR.MFG) DESC;
0
 
ste5anSenior DeveloperCommented:
You already said it, thus just use it:

SELECT CR.MFG, Count(*) AS CountOfMFG
FROM AGI_CROSS_REVIEW_2 CR
WJERE Len(CR.MFG & "") > 4
GROUP BY CR.MFG
ORDER BY Count(CR.MFG) DESC;

Open in new window

0
 
FordraidersAuthor Commented:
but i also need to exclude  records where a Numeric value exists in the MFG field.

fordraiders
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ste5anSenior DeveloperCommented:
SELECT CR.MFG, Count(*) AS CountOfMFG
FROM AGI_CROSS_REVIEW_2 CR
WJERE Len(CR.MFG & "") > 4 
AND NOT IsNumeric(CR.MFG & "")
GROUP BY CR.MFG
ORDER BY Count(CR.MFG) DESC;

Open in new window

0
 
FordraidersAuthor Commented:
DALE, actually yes...Thanks
0
 
PatHartmanCommented:
This makes for very inefficient queries.  Wouldn't it be better to not allow the invalid data to begin with.
1
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.