Hi

I want to select a few records from a database, but I'm having trouble pinning down the correct method for my SQL call.

I have a SET field with the following options:

'Wales','Mid Wales','North Wales','South Wales','West Wales','Towns & Cities in Wales'

I want my search program to search for a record that has is in 'Wales'. However, when I try:

res_place LIKE '%Wales%'

That returns all records that are in 'Wales','Mid Wales','North Wales' and so forth.

So how do I create a SQL call that will match the option 'Wales' and nothing else?

Thanks for your help.

- wil

what about

res_place = 'Wales'

?

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Bang on Yogi

However Wil could have found the answer by reading the docs like he suggests to others.

http://www.mysql.com/doc/S/E/SET.html

No.

Re-read the message. This is a SET field, NOT an ENUM field.

Therefore, a record could have multiple values such as 'Wales, North Wales, Mid Wales'.

Therefore Therefore... the statement = 'Wales' would NOT work, as the database does not just retun 'Wales' but it returns 'Wales, North Wales, Mid Wales'. So it doesn't match it at all.

- wil

The correct answer to my question is to use the FIND_IN_SET() function.

Thank you for your efforts, anyway.

- wil

>>Re-read the message. This is a SET field, NOT an ENUM field.<<

Um yeah I know, why do you think I pasted the SET doc url above

Go and read the url I gave you. You will then realise that ='Wales' is valid.


Normally, you perform a SELECT on a SET column using the LIKE operator or the FIND_IN_SET() function:

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;


But the following will also work:

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

Paul

Think about it.

A field contains:

'Wales, North Wales, South Wales'

Tell me (then try your code out! ! ! ! !) why a search on:

field = 'Wales'

will NOT work.

Of course it will work if 'Wales' is the ONLY value in the field (like an ENUM colum) but if multiple values have been selected that will be useless and you will need to use FIND_IN_SET() function - which is what my original message asked for. Therefore Yogi was not "Bang on" he was "bang off". I thank him for trying, though.

- wil

Whatever Wil, I don't really care

I can do without arguments at the moment and there's only way to achieve that.

Bye.

But I do care that the information you provided is not relevant to the question asked. And then you go about undermining the question with a sarcastic tone.

If your original response would of answered the question correctly, then it would be far more appreciated than an irrelevant answer and the sarcastic tone.

Think before you post.

Good day,

- wil

>>But I do care that the information you provided is not relevant to the question asked.<<

I only made two posts in the thread before you started getting ratty, one of which provided a link which eventually gave you the answer to your question, the other pointed out what the docs said, both were related to mysql and SET specifically. I'm not quite sure how that makes the posts irrelevant.

Now for the last time, goodbye