Mysql FIND_IN_SET () with semicolon instead of comma

I have a database set that looks something like this:

Table: Notes
    nid | forDepts
    --------------
    1   | 1;2;4
    2   | 4;5


Table: Positions
    id  | name
    --------------
    1   |  Executive
    2   |  Corp Admin
    3   |  Sales
    4   |  Art
    5   |  Marketing

      

This query will work if the data forDepts

was comma separated

SELECT  a.nid,
        GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM    Notes a
        INNER JOIN Positions b
            ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP   BY a.nid

      

Is there a way to match it with a semicolon separator? Or is there a better way to do this? Both of my tables are quite large (5336 and 930 rows).

I could make 2 requests and blow up ;

and match accordingly, but if there is a better way that can be done in a single request that would be great.

Here is my sqlfiddle

+3


source to share


1 answer


You can replace commas:



FIND_IN_SET(b.id ,REPLACE(a.forDepts, ';', ',') )

      

+3


source







All Articles