String functions
MySQL string functions help you measure, extract, compare, clean and transform text stored in columns. Common tasks include checking character length, extracting part of a value, trimming spaces, padding text and separating an email user name from its domain.
SELECT CHAR_LENGTH('plus2net'), LEFT('plus2net', 4), TRIM(' SQL ');Use these functions with SELECT for analysis and with UPDATE when you intentionally need to change stored values.
MySQL string functions we can use to manipulate data and derive useful information and analysis from the tables. These functions can be used along with update commands to change records or can be used to update multiple tables.If you have a table of your newsletter subscribers and you want to know how many are using gmail then you have to work on the email address and extract the domain part from it. You may also like it see the all the email providers in a single list with number of subscribers. Such applications are possible by using mysql string functions.
We will discuss some of the string functions and develop some applications using them.
String function reference
| Function | Description |
|---|---|
| CONCAT() | Appending string data |
| CONVERT() | Converting one data type to other |
| CHARACTER_LENGTH() | number of characters present in data |
| CHAR_LENGTH() | number of characters present in a string |
| ELT() | Return the string based on position |
| FIELD() | Return the position of the searched string |
| FIND_IN_SET() | searching string within a set of strings |
| LOCATE() | searching the string & getting location of presence |
| LOWER() / UPPER() | Changing lower case to upper case and vies versa |
| LENGTH() | Length of the string in bytes |
| LEFT() | Getting part of the string from left |
| LPAD() / RPAD() | Pad string from Left |
| LTRIM() | Remove blank space from Left |
| RTRIM() | Remove blank space from Right |
| REPLACE() | Updating part of the data |
| REVERSE() | Reversing a string by Query |
| REPEAT() | Repeating string n number of times |
| RIGHT() | Part of a string towards right |
| SPACE() | Adding Space |
| STRCMP() | String Comparison |
| SUBSTRING() | Getting Part of the string |
| SUBSTRING_INDEX() | Part of the string using delimiter |
| TRIM() | Removing matching string from both ends of a string |
Practical string projects
| Project | Description |
|---|---|
| Domain , Userid | Updating table with Domain and Userid part of Email address |
| String Count | Counting presence of search string in a main string |
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Connect:
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
SQL Video Tutorials