Archives

The List

« MySQL Pop Quiz #17 | Main | MySQL Pop Quiz #18 »

FIND_IN_SET() :: MySQL Function of the Day

By Carsten | March 25, 2008

Function name: FIND_IN_SET
Aliases: -
Function type: String function
Purpose: Return position of value in string of comma-separated values

Description: FIND_IN_SET() looks for the first occurrence of a string within another string containing comma-separated values:

FIND_IN_SET('r','c,a,r,s,t,e,n, ,p,e,d,e,r,s,e,n') => 3

Usage example: Suppose I have a table of vehicle types and colors, representing the available color range for vehicles in my shop. No type/color combination can appear more than once, so the primary key spans (type, color). It holds the following data:

mysql> select * from vehicle_colors;
+------+-------+
| type | color |
+------+-------+
| bike | blue  |
| bike | green |
| suv  | blue  |
| van  | green |
+------+-------+

If I want to know

I can get this information with a single query like so:

SELECT type, SUM(FIND_IN_SET(color, 'blue,green')) AS which_colors
FROM vehicle_colors
WHERE color IN ('blue', 'green')

GROUP BY type;
+------+--------------+
| type | which_colors |
+------+--------------+
| bike |            3 |
| suv  |            1 |
| van  |            2 |
+------+--------------+

The output tells me that bikes, suvs and vans may all be found in either blue or green (the fact that they are listed at all) and that bikes are available in both colors (1+2=3), suvs in blue only (1) and vans in green only (2).

MySQL manual entry on FIND_IN_SET()

Thanks to Baron for suggesting this entry.

[fadps]

Topics: MySQL Function of the day |

4 Responses to “FIND_IN_SET() :: MySQL Function of the Day”

  1. Amit Says:
    October 20th, 2008 at 7:38 am

    Hello Carsten ,

    I need to sort mysql result starting with a field value like ‘Canada’ and then all cities in alphabetical order.

    Can u please help me out ASAP .???

    Thanx in Advance !

  2. Rose Says:
    April 28th, 2011 at 9:58 am

    Use ORDER BY ASC

  3. Novak Says:
    July 2nd, 2012 at 10:23 am

    FIND_IN_SET() looks for the first occurrence of a string within another string containing comma-separated values -
    what do I use, if I wan’t to look and list out ones with most matches?
    Search: blue and red camari
    List out only ones that have both blue and red in column….

  4. manoj Says:
    October 13th, 2012 at 8:09 am

    thanks to help to me

Comments