MySQL string functions. Part 1

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:
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

The results will be as following:
Aida YespicaA3212
Anna MatiasA532
Antonella MusentahA53545253
Barbara SpearsB6162162
Bruce DavenportB62315163

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:
12 Famke Bacher F52126 F52126 12 famke bacher