|
|
MySQL have a vast collection of string functions that make it possible to
achieve almost every manipulative objective. In the following query we used
SUBSTRING_INDEX() function to select
only first two words from the item-name (supposing that word's delimeter is a
space CHAR(32)). As well first 25 characters
from the name is selected by the help of MID()
function. LOCATE() is used to
identify position of substring '50' in the item-code:
SELECT ItemCode, SUBSTRING_INDEX(ItemName,
CHAR(32),2) AS First_2words,
MID(ItemName,1,25) AS ItemName_Starting25char
FROM Items
WHERE LOCATE('50',ItemCode) = 1
|
The results will be as following:
| ItemCode | First_2words | ItemName_Starting25char |
| 5003258 |
Philips DVP642 |
Philips DVP642 DivX-Certi |
| 5003298 |
Philips DVP5140 |
Philips DVP5140 Multiform |
| 5003501 |
TDK Electronics |
TDK Electronics 4.7GB/16x |
| 5003502 |
TDK Electronics |
TDK Electronics DVD+R47FC |
REPLACE(STR,From_STR,To_STR) function
returns the string STR with all occurrences of the string From_STR replaced
by the string To_STR:
SELECT ItemCode, ItemName, REPLACE(ItemName,'DVD Player', 'Player')
FROM Items
|
SOUNDEX() function
returns a soundex code from the input string. Two strings that sound almost
the same should have identical soundex code:
SELECT CustomerName, SOUNDEX(CustomerName) AS SOUNDEX_Code
FROM Customers
ORDER BY SOUNDEX(CustomerName)
|
The results will be as following:
| CustomerName | SOUNDEX_Code |
| Aida Yespica | A3212 |
| Anna Matias | A532 |
| Antonella Musentah | A53545253 |
| Barbara Spears | B6162162 |
| Bruce Davenport | B62315163 |
Now using SOUNDEX() we will check if there
any customer of Colombo Ltd. whos name sounds like the company employee name:
SELECT a.CustomerID, a.CustomerName, SOUNDEX(a.CustomerName)
AS Cust_Soundex,
SOUNDEX(CONCAT(b.FirstName,b.LastName)) AS Emp_Soundex,
b.EmpNo, b.FirstName, b.LastName
FROM Customers AS a, Employees AS b
WHERE SOUNDEX(a.CustomerName) =
SOUNDEX(CONCAT(b.FirstName,b.LastName))
|
To our greatest surprise Famke Bacher's name sounds the same as a name of one
of Colombo customers:
| CustomerID | CustomerName | Cust_Soundex | Emp_Soundex | EmpNo | FirstName | LastName |
| 12 |
Famke Bacher |
F52126 |
F52126 |
12 |
famke |
bacher |
|