Introduction
The substring returns the substring as specified and an index is a data structure. So, the substring index returns a substring from a string before the specified number of occurrences of the delimiter. Here we have to explain the using; there are many examples. Indexes retrieve rows from other tables when joins are performed and it shows how to use a substring to return a string and define it's functionality. In MySQL, an index is a data structure in my databse. By using the substring index to sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key.
Prep-end a sufficient number of periods to the table name values
We have selected a column using a substring index and concatenated the name as the leftmost and as the middle from the table.
Command
mysql-> SELECT name,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1)
-> AS leftmost,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1)
-> AS middle,
-> SUBSTRING_INDEX(name,'.',-1) AS rightmost
-> FROM myhostname;
Get to the First and Third segments or second and fourth segments from the id values
Here the substring index segment the column values according to the given command. We we have selected a first and third segment.
Command
mysql-> SELECT
-> id,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',1),'-',-1) AS segment1,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',3),'-',-1) AS segment3
-> FROM house1;
Command
mysql-> SELECT
-> id,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1) AS segment2,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1) AS segment4
-> FROM house1;
To return all field values the right or left of a given character
Here first of all select the table "mytable9" and it contains a column with eight rows.
Command
We have selected a substring index with two things from a table. It returns from the table for each fields to the right or left of a given character, use SUBSTRING_INDEX(str,c,n).
mysql -> select SUBSTRING_INDEX(name,'r',2), SUBSTRING_INDEX(name,'i',-1) FROM mytable9;
Using procedure analyses to get information
By using the procedure analyses command we get the some useful information about the data.
Command
mysql> select * from player procedure analyse(0,0)\G
Index
It shows the simple index of a table. Looks like as:
Resources
Here are some useful resources