Working With a Sub String Index in MySQL


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;

sufficient number 1.gif

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;

1 and 3 segments.gif

Command

mysql-> SELECT
  ->  id,
  ->  SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1) AS segment2,
  ->  SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1) AS segment4
  ->  FROM house1;


2 and 4 segment.gif

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.

select mytable 9.gif

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;

substring 2.gif

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

procedure.gif

Index

It shows the simple index of a table. Looks like as:


index.gif

Resources

Here are some useful resources

Up Next
    Ebook Download
    View all
    Learn
    View all