read http://www.mysql.com/doc/S/E/SET.html for the set, how it works
internally...
humm.. is that what you want? :
mysql> CREATE TABLE test(
-> id INT UNSIGNED AUTO_INCREMENT,
-> place SET("Whales","North","South");
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)
[insert some values]
mysql> SELECT * FROM test;
+----+--------------+
| id | place |
+----+--------------+
| 1 | Whales,North |
| 2 | Whales,South |
| 3 | North,South |
+----+--------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM test WHERE place LIKE "%Whales%";
+----+--------------+
| id | place |
+----+--------------+
| 1 | Whales,North |
| 2 | Whales,South |
+----+--------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM TEST WHERE FIND_IN_SET('Whales',place)>0;
+----+--------------+
| id | place |
+----+--------------+
| 1 | Whales,North |
| 2 | Whales,South |
+----+--------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM TEST WHERE place & 1;
+----+--------------+
| id | place |
+----+--------------+
| 1 | Whales,North |
| 2 | Whales,South |
+----+--------------+
2 rows in set (0.00 sec)
[This one is the best one IMO]
or even if you wanna be specific:
mysql> SELECT * FROM TEST WHERE place = "Whales,North" OR place =
"Whales,South";
+----+--------------+
| id | place |
+----+--------------+
| 1 | Whales,North |
| 2 | Whales,South |
+----+--------------+
2 rows in set (0.00 sec)
But i don't suggest this one!
HTH
Etienne
Wiliam Stephens wrote:
>
> Hi
>
> Thanks for your reply.
>
> I've got the field "res_places_sl". It's a SET field that consists of the
> following options:
>
> 'Wales','North','South'
>
> I was using FIND_IN_SET('Wales',res_places_sl); to access all records that
> were categorised under 'Wales' which worked great.
>
> Now, I want to be able to access them from their numerical position. i.e.
> if I wanted to access 'Wales' I would ideally want to use something like
>
> FIND_IN_SET('1',res_places_sl);
>
> But that doesn't seem to want to work. The reason I want this is that I
> don't want to pass the category name in the URL, but passing a numerical
> reference would be ideal.
>
> Is there a way to do this?
>
> Thanks for your help.
>
> Wil
>
> At 16:54 10/12/01, you wrote:
> >I think you are missing something.
> >
> > >From the Manual:
> >
> >FIND_IN_SET(str,strlist)
> > Returns a value 1 to N if the string str is in the list strlist
> >consisting of N substrings. A string list is a string composed of
> >substrings separated by `,'
> > characters. If the first argument is a constant string and the
> >second is a column of type SET, the FIND_IN_SET() function is optimised
> >to use bit arithmetic!
> > Returns 0 if str is not in strlist or if strlist is the empty
> >string. Returns NULL if either argument is NULL. This function will not
> >work properly if the first
> > argument contains a `,':
> >
> > mysql> SELECT FIND_IN_SET('b','a,b,c,d');
> > -> 2
> >=====
> >
> >I don't know what exactly you're trying to acheive, but I'm pretty sure
> >FIND_IN_SET is not what you are looking for.
> >
> >Can you give table descriptions and what is the data you want at the end
> >of your query?
> >
> >Etienne
> >
> >Wiliam Stephens wrote:
> > >
> > > Hi
> > >
> > > How can I create an index of a SET field so that a FIND_IN_SET can search
> > > for a column number rather than name?
> > >
> > > Is this even possible in MySQL? It might seem like a bug?
> > >
> > > Thanks.
> > >
> > > Wiliam Stephens
> > >
> > > Web Developer
> > > http://www.fbagroup.co.uk
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >--
> >Etienne Marcotte
> >Specifications Management - Quality Control
> >Imperial Tobacco Ltd. - Montreal (Qc) Canada
> >514.932.6161 x.4001
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Etienne Marcotte
Specifications Management - Quality Control
Imperial Tobacco Ltd. - Montreal (Qc) Canada
514.932.6161 x.4001
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
