How can we find the index position of a string stored as a record in MySQL table’s column?
We can use FIELD() function to find the index position of a string stored as a record in MySQL table’s column. To demonstrate it we are using the table named ‘websites’ having the following data
Example
mysql> Select * from websites;
+----+---------------+------------------------+
| Id | Purpose | Webaddress |
+----+---------------+------------------------+
| 1 | For tutorials | www.tutorialspoint.com |
| 2 | For searching | www.google.co.in |
| 3 | For email | www.gmail.com |
+----+---------------+------------------------+
3 rows in set (0.00 sec)
Now, suppose if we want to find out the index number of a particular string say ‘for email’ from the strings stored as a record in ‘Purpose’ and ‘Webaddress’ columns of this table then the following query will do it −
mysql> Select FIELD('For email', purpose, webaddress) From websites;
+----------------------------------------+
| FIELD('For email', purpose, webaddress)|
+----------------------------------------+
| 0 |
| 0 |
| 1 |
+----------------------------------------+
3 rows in set (0.00 sec)The above result set shows that ‘For email’ string is at first index of the third row.
mysql> Select FIELD('www.tutorialspoint.com', purpose, web address) From websites;
+------------------------------------------------------+
| FIELD('www.tutorialspoint.com', purpose, web address)|
+------------------------------------------------------+
| 2 |
| 0 |
| 0 |
+------------------------------------------------------+
3 rows in set (0.00 sec)The above result set shows that ‘www.tutorialspoint.com’ string is at the second index of the first row.
Published on 21-Feb-2018 11:46:44
