Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: How to compare comma separated strings in MYSQL

    Hi,

    Could you help me in doing the comparing of comma separated strings using sql query in MYSQL.


    ex:table has column

    Column
    a
    a,b
    a,b,c

    how can i compare the H =(a,b,c) using sql query in MYSQL
    Note:H is the String has value a,b,c

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You should look at your database design if you need to search content within a string. I would have these separated out into another table. For example if this is linked with a customer and a,b and c are attributes I would create another table called customer_attributes which contains just a customer_id and an attribute value.

    Search performance would be improved significantly by creating an index on the attribute field so that it can return the rows quickly without the need to scan the entire table.

    If you want to continue doing it this way the query you would need to use would be

    Code:
    mysql> create table customers ( customer_id int, attributes varchar(100));
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into customers(customer_id, attributes) values (1,'a,c'),(2,'a'),(3,'b,c'),(4,'c'),(5,'a,b'),(6,'b');
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    
    mysql> select * from customers;
    +-------------+------------+
    | customer_id | attributes |
    +-------------+------------+
    |           1 | a,c        |
    |           2 | a          |
    |           3 | b,c        |
    |           4 | c          |
    |           5 | a,b        |
    |           6 | b          |
    +-------------+------------+
    6 rows in set (0.00 sec)
    
    mysql> SELECT * FROM customers WHERE attributes = 'a' OR attributes LIKE 'a,%' OR attributes LIKE '%,a,%' OR attributes LIKE '%,a';
    +-------------+------------+
    | customer_id | attributes |
    +-------------+------------+
    |           1 | a,c        |
    |           2 | a          |
    |           5 | a,b        |
    +-------------+------------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT * FROM customers WHERE attributes = 'b' OR attributes LIKE 'b,%' OR attributes LIKE '%,b,%' OR attributes LIKE '%,b';
    +-------------+------------+
    | customer_id | attributes |
    +-------------+------------+
    |           3 | b,c        |
    |           5 | a,b        |
    |           6 | b          |
    +-------------+------------+
    3 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2015
    Posts
    2

    How to compare comma separated strings in MYSQL

    Hi,

    Thanks for the solution.
    is it possible to select single (exact) value.

    mysql> SELECT * FROM customers

    CONDITION_ID COMBINED_TABLE_NAME
    1 pv_org,pv_inv,pv_value
    2 pv_inv,pv_org
    3 pv_org,pv_auction,pv_value

    mysql> SELECT * FROM customers WHERE COMBINED_TABLE_NAME = 'pv_org,pv_inv,pv_value'

    CONDITION_ID COMBINED_TABLE_NAME
    1 pv_inv,pv_org (i need output like this .....is this possible)

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It is possible but I am not sure why you would want this? Surely if you return the rows you can then handle the content and processing of the list within the application code?

    If not then look at using INSTR function which shows whether something exists in a string. Again I would look very closely at your database model. If you had these in a separate table then you would be able to get your queries fulfilled very quickly and in an efficient way.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •