Syntax error MySQL query to get string from one column and find its position in another column with comma separated values?

MySQL query to get string from one column and find its position in another column with comma separated values?

For this, use FIND_IN_SET(). Let us first create a table −

mysql> create table DemoTable1866
     (
     Value1 int,
     ListOfValues varchar(100)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1866 values(56,'78,56,98,95');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1866 values(103,'103,90,102,104');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1866 values(77,'34,45,77,78');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1866;

This will produce the following output −

+--------+----------------+
| Value1 | ListOfValues   |
+--------+----------------+
|     56 | 78,56,98,95    |
|    103 | 103,90,102,104 |
|     77 | 34,45,77,78    |
+--------+----------------+
3 rows in set (0.00 sec)

Here is the query to get string position in another column −

mysql> select Value1,ListOfValues,find_in_set(Value1,ListOfValues) as Output from DemoTable1866;

This will produce the following output −

+--------+----------------+--------+
| Value1 | ListOfValues   | Output |
+--------+----------------+--------+
|     56 | 78,56,98,95    |      2 |
|    103 | 103,90,102,104 |      1 |
|     77 | 34,45,77,78    |      3 |
+--------+----------------+--------+
3 rows in set (0.00 sec)
Updated on: 2019-12-26T07:19:48+05:30

298 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements