Working With a Sub String Index in MySQL

Arjun Panwar
12y
15.6k
0
0

Article

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;

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.

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