WHERE a='a' OR a='12' 1

[smashing]

Please excuse my ignorance here. I have a column that contains numbers or letters. I'm trying to select something that matches either this or that. For example I'm working with a table containing a list of kids at a school. I want to pull any number between 1 and 12 so in my SELECT query I have this:

AND kids.grade>'0' AND kids.grade < '13'

but I aslo want to pull the kindergarten kids that have alphbetical codes for their grades so:
AND kids.grade='PN1'

How can these 2 be combined in 1 query?

 

[r937]

where kids.grade between '1' and '12'
or kids.grade='PN1'




r937.com | rudy.ca

 

[smashing]

thanks man I'll give it a shot.

 

[smashing]

What's happening now using just the following:

AND kids.grade between '1' and '8'

is that it'll select grades 11 and 12 as well.
This is due to the field being a VARCHAR type. If I change the field type to the INT type all the alphabetical stuff inside are changed to a zero. What would be the best type in this scenario when we really have mixed content here?

 

[r937]

best would be this --

where kids.grade
in ('PN1','1','2','3','4','5','6','7','8')


r937.com | rudy.ca

 

[TonyGroves]

Rudy's solution is fine for small value sets, but if you want to have the flexibility to deal with bigger ranges, you might consider another approach.

In a string comparison, the value '11' is between '1' and '8', so you would need to ensure that a numeric comparison is done instead:[tt]
WHERE kids.grade BETWEEN 1 AND 8[/tt]
Then, if any of your alphanumeric codes start with one or more digits, those leading digits will be taken as the field's numeric value, so that '07AB' evaluates to 7, which is between 1 and 8. If this is a possibility and you want such fields to be regarded as non-numeric, then you would need to add another condition, such as:[tt]
WHERE (
kids.grade BETWEEN 1 AND 8
AND kids.grade REGEXP '^[0-9]*$'
)[/tt]
or:[tt]
WHERE (
kids.grade BETWEEN 1 AND 8
AND CAST(kids.grade+0 AS CHAR) = kids.grade
)[/tt]
And finally of course:[tt]
OR kids.grade = 'PN1'[/tt]

 

[r937]

tony is right, i didn't realize that the number of different grades would be too many to list

;-)

r937.com | rudy.ca