Post
Ask Question

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 its 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," it contains a column with eight rows.

select mytable

Command

We have selected a substring index with two things from a table. It returns from the table for each field 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 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


Similar Articles