Mysql text processing function example (use of data processing function 1)

Text processing function

#Before we talked about the rtrim() function used to remove spaces at the end of a string, this is how to use the function to process this article.

Next we introduce another function, the upper() function:

Input:

select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name;

Output:

Analysis: As you can see, upper() converts the text to uppercase, so in this example each vendor is listed twice, first as the value stored in the vendors table, and secondly as vend_name_upcase converted to uppercase.

The following table lists some commonly used text processing functions:

The soundex in the table requires further explain. Soundex is an algorithm that converts any text string into an alphanumeric pattern that describes its phonetic representation. soundex takes into account similar-sounding characters and syllables, allowing strings to be compared phonetically rather than letter-wise. Although soundex is not a SQL concept, MySQL provides support for soundex.

The following is an example of using the soundex() function. There is a customer Coyote Inc. in the customers table, whose contact name is Y.Lee. But what if this is a typo and the contact name should actually be Y.Lie? Obviously, searching by the correct contact name will not return data, as shown below:

Input:

select cust_name,cust_contact from customers where cust_contact = 'Y.Lie';

Output:

Now Try searching using the soundex() function, it matches all contact names that sound similar to Y.Lie:

Input:

select cust_name,cust_contact from customers where soundex(cust_contact) =soundex('Y.Lie');

Output:

Analysis: In this example, the where clause uses the soundex() function to convert the cust_contact column value and search string into their soundex values. Because Y.Lee and Y.Lie sound similar, their soundex values ​​match, so the where clause correctly filters out the required data.

【Related recommendations】

1. MySQL online free video tutorial
   

2. HanShunping2016The latest MySQL basic video tutorial

3. Examples of date and time processing functions and numerical processing functions (Usage of data processing functions 2)

The above is the detailed content of Mysql text processing function example (use of data processing function 1). For more information, please follow other related articles on the PHP Chinese website!