SQL Question

chinto

Hey folks,

I need a little help with a SQL query. I am looking to get a range but trying to limit to the first 4 numbers of a code. The table looks like this:

Table Categories:

Cat_ID-----Code----Title
1------------4111------Beverages
2------------41111----CocaCola
3------------41112----Pepsi Cola
4------------41113----Dr. Pepper
5------------4112-----Snacks
6------------41121----Potatoe Chips
7------------41122----Cheese Curls
8------------4113------Sandwhiches
9------------41131----Grilled Cheese
10----------41132-----Cheese Burger

What I need to do is pull the primary categories like beverages(4111), Snacks(4112), and Sandwhiches(4113). I need to do this without pulling the subcategories like cocacola, pepsi etc.

I have tried Like '411%' but that pulls all categories, my question is there a way to to pull just the 4 digit codes that are like 411?

Thank you in advance for your help.

LordShryku

If it's only 4 digit codes, you want use
SELECT blah from TABLE where FIELD = '411?';

chinto

The '?' didnt work for me for some reason, it retrieved 0 results. But I managed to accomplish this using a BETWEEN query after all.

Now I am passing a variable to the next page called $ID, that carries the main category code. I want to pull the subcategories pertaining to the main category passed in the variable. For this I am using a LIKE '$ID%' but the problem is it also pulls the main category and lists it again. I would like to exclude the code carried in the variable $ID but still get all the like codes.

So in essence i need to get all the codes starting with 4111 but not 4111 its self. Im hoping someone knows a pretty simple solution to this.

Thanks in advance,

Sxooter

Well, since you didn't mention WHICH database, I'll tell you two ways to do it in postgresql.

select * from table where id like '444_';

OR
select * from table where substr(id,0,4) = '444';

chinto

Im sorry forgot to mention its a MySQL database. Thanks for the reply. 🙂

chinto

Got it to work with a LIKE '$ID%' AND NOT LIKE '$ID';

Thanks for the help folks.

Sxooter

Did you get to try to see if like '444_' worked? That's the simplest, easiest to understand solution for me. I don't know how good / just plain different MySQL's regex stuff is.