To return everything to the right or left of a given character, use SUBSTRING_INDEX(str,c,n). : SUBSTRING_INDEX « String « SQL / MySQL

1. SQL / MySQL
2. String
3. SUBSTRING_INDEX

To return everything to the right or left of a given character, use SUBSTRING_INDEX(str,c,n).

     
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  name    VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mytable (name)
    ->  VALUES
    ->          ('copper'),
    ->          ('gold'),
    ->          ('iron'),
    ->          ('lead'),
    ->          ('mercury'),
    ->          ('platinum'),
    ->          ('silver'),
    ->          ('tin')
    -> ;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM mytable;
+----------+
| name     |
+----------+
| copper   |
| gold     |
| iron     |
| lead     |
| mercury  |
| platinum |
| silver   |
| tin      |
+----------+
8 rows in set (0.00 sec)

mysql>
mysql> select SUBSTRING_INDEX(name,'r',2), SUBSTRING_INDEX(name,'i',-1) FROM mytable;
+-----------------------------+------------------------------+
| SUBSTRING_INDEX(name,'r',2) | SUBSTRING_INDEX(name,'i',-1) |
+-----------------------------+------------------------------+
| copper                      | copper                       |
| gold                        | gold                         |
| iron                        | ron                          |
| lead                        | lead                         |
| mercu                       | mercury                      |
| platinum                    | num                          |
| silver                      | lver                         |
| tin                         | n                            |
+-----------------------------+------------------------------+
8 rows in set (0.00 sec)

mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
  

Related examples in the same category