How can we fetch a MySQL SET column as a list of integer offset?

MySQLMySQLi Database

<p style="">We can fetch the MySQL SET column values as a list of integer offset with the help of the MAKE_SET() function. To make it understand, we are creating a table named &lsquo;set_testing&rsquo; as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; Create table set_testing( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, table SET(&#39;ABC&#39;,&#39;ABD&#39;,&#39;GHF&#39;) NOT NULL); Query OK, 0 rows affected (0.08 sec) mysql&gt; Insert into set_testing (table) values(&#39;1&#39;); Query OK, 1 row affected (0.06 sec) mysql&gt; Insert into set_testing (table) values(&#39;2&#39;); Query OK, 1 row affected (0.06 sec) mysql&gt; Insert into set_testing (table) values(&#39;3&#39;); Query OK, 1 row affected (0.02 sec) mysql&gt; Insert into set_testing (table) values(&#39;4&#39;); Query OK, 1 row affected (0.02 sec) mysql&gt; Select * from set_testing; +----+---------+ | id | table &nbsp; | +----+---------+ | 1 &nbsp;| ABC &nbsp; &nbsp; | | 2 &nbsp;| ABD &nbsp; &nbsp; | | 3 &nbsp;| ABC,ABD | | 4 &nbsp;| GHF &nbsp; &nbsp; | +----+---------+ 4 rows in set (0.00 sec)</pre><p style="">Now, the following query will fetch the MySQL SET column as a list of integer offset &minus;</p><pre class="prettyprint notranslate">mysql&gt; Select MAKE_SET(types+0,&#39;1&#39;,&#39;2&#39;,&#39;3&#39;) as output from doctypes1; +--------+ | output | +--------+ | 1 &nbsp; &nbsp; &nbsp;| | 2 &nbsp; &nbsp; &nbsp;| | 1,2 &nbsp; &nbsp;| | 3 &nbsp; &nbsp; &nbsp;| +--------+ 4 rows in set (0.00 sec)</pre>
raja
Updated on 22-Jun-2020 06:25:01

Advertisements