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.

Swarali Sree

Published on 21-Feb-2018 07:16:44

• Related Questions & Answers
• How can we add day/s in the date stored in a column of MySQL table?
• How can we get some last number of characters from the data stored in a MySQL table’s column?
• How can we get some starting number of characters from the data stored in a MySQL table’s column?
• How can we modify column/s of MySQL table?
• Can we use reserved word ‘index’ as MySQL column name?
• How can we add a time interval to date stored in a column of MySQL table?
• How can we amend the declared size of a column’s data type in MySQL?
• How to alter the data type of a MySQL table’s column?
• How can we search a record from MySQL table having a date as a value in it?
• Store a column's value into a MySQL stored procedure’s variable
• In MySQL, which function we can use to find the index position of a particular string from a list of strings?
• How can we apply BIT_LENGTH() function on the column/s of MySQL table?
• How can we get the structure of a MySQL view as we can get the structure of a MySQL table?
• How can we get the definition of a MySQL view as we can get the definition of a MySQL table?
• Update a column based on another MySQL table’s column