MySQL table with multiple values in a field

Question

I have a MySQL table in which multiple values occurs in some rows. In the example below such values are separated by vertical bar (|).

+---------+------+
| cui     | type |
+---------+------+
| 100     | A    |
| 101|102 | A    |
| 103     | B    |
| 104|103 | C    |
+---------+------+

I wonder how to retrieve only the first value if multiple values are present. For example, the table above should be returned as

+---------+------+
| cui     | type |
+---------+------+
| 100     | A    |
| 101     | A    |
| 103     | B    |
| 104     | C    |
+---------+------+

Any suggestions are greatly welcome.

Abhik Chakraborty · Accepted Answer · 2014-11-08 17:06:19Z

1

You can use the function substring_index()

mysql> select substring_index('101|102','|',1);
+----------------------------------+
| substring_index('101|102','|',1) |
+----------------------------------+
| 101                              |
+----------------------------------+

So the query becomes

select substring_index(cui,'|',1) as cui,
type
from table_name

answered Nov 8, 2014 at 17:06

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL table with multiple values in a field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related