Tagged: mysql

Getting a Substring in MySQL


I just learned how to get substring of specific range using LOCATE() and SUBSTRING().

Let’s say we have email addresses and want to get username part from them.

Step 1 Locate the index of ‘@’ character

LOCATE('@', email)

Step 2 Get substring using it.

SUBSTRING(email, 1, LOCATE('@', email) - 1)

Note that the starting index is 1 and the the len part must be 1 minus the location.

Wait.

There is a function exactly does this. SUBSTRING_INDEX().

SUBSTRING_INDEX(email, '@', 1)

installing northwindextended mysql


download the script

~/tmp$ wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/northwindextended/Northwind.MySQL5.sql

connect to mysql

~/tmp$ mysql -u root -p
Enter password:
...
mysql >

execute the script

mysql> source Northwind.MySQL5.sql
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| northwind          |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

mysql>

create a user and grant

mysql> create user 'northwind'@'%' identified by 'northwind';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on northwind.* to 'northwind'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

list the tables

onacit@VM1451490420734:~/tmp$ mysql -u northwind -p
Enter password: 
...

mysql> use northwind;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------------+
| Tables_in_northwind            |
+--------------------------------+
| Alphabetical list of products  |
| Categories                     |
| Category Sales for 1997        |
| Current Product List           |
| Customer and Suppliers by City |
| CustomerCustomerDemo           |
| CustomerDemographics           |
| Customers                      |
| EmployeeTerritories            |
| Employees                      |
| Invoices                       |
| Order Details                  |
| Order Details Extended         |
| Order Subtotals                |
| Orders                         |
| Orders Qry                     |
| Product Sales for 1997         |
| Products                       |
| Products Above Average Price   |
| Products by Category           |
| Quarterly Orders               |
| Region                         |
| Sales Totals by Amount         |
| Sales by Category              |
| Shippers                       |
| Summary of Sales by Quarter    |
| Summary of Sales by Year       |
| Suppliers                      |
| Territories                    |
+--------------------------------+
29 rows in set (0.00 sec)

mysql>