MySQL String Functions in Action

What’s so special about MySQL? Why do most developers choose this relational system for managing databases? MySQL is widely used primarily because it’s based on the Structured Query Language (SQL), which is a perfect fit for accessing and handling records. Additionally, MySQL attracts users with its scalability and robust performance. This post will focus on another advantage that should be mentioned to highlight the value of MySQL.

MySQL has a set of built-in functions for data processing, transformation, and analysis. In particular, the following discussion will be about string functions. dbForge Studio for MySQL offers extensive support for working with MySQL string functions and makes it simple to develop and manage databases. That’s why all the examples will be illustrated in Studio. So, let’s find out what string functions are and when to use them.

 

Contents

• What are MySQL string functions?
• Types of MySQL string functions
• Determine the convertibility of strings to numeric data types
• Remove extra spaces in strings
• Word count determination in strings with a delimiter
• Strategies for transforming table rows into lists with delimiters
• Alter delimited lists into table rows
• Capitalize every word in a line
• Extract the nth word from a string
• Randomize character order in strings
• Convert delimited lists to the WHERE <column_name> IN (…) clause
• Reasons behind utilizing GUI tools for MySQL queries
• Key query editing features of dbForge Studio for MySQL
• Conclusion

What are MySQL string functions?

MySQL string functions are designed to perform operations on character strings or text data stored in databases. With these functions, it’s possible to search for specific substrings within strings, find the position of a substring, check for the existence of a substring, or perform case-sensitive or case-insensitive comparisons between strings.

String functions allow performing such tasks as:

• Manipulation: Modify the case, trim spaces, concatenate strings, extract substrings, and replace characters or substrings within strings.
• Search and comparison: Look for specific substrings and find their position, check for existing substrings, and compare strings.
• Data validation: Verify input strings and get specific information from structured text data.
• Text analysis and formatting: Calculate string lengths, count occurrences of specific characters or substrings, and reverse strings.

Types of MySQL string functions

The types of MySQL string functions can be categorized into several groups based on their functionality. Here are some common ones.

Manipulation functions

These functions modify the content or structure of strings. For example:

• CONCAT() returns a concatenated string
• SUBSTRING() extracts substrings
• REPLACE() substitutes characters or substrings within strings

Analysis and formatting functions

They are used for analyzing and formatting text data. For example:

• LENGTH() outputs the length of a string in bytes
• CHAR_LENGTH() measures the length of a string in characters
• INSTR() retrieves the position of the first occurrence of a substring in a string

Conversion functions

These are the functions that transform one data type into another. For example:

• CAST() converts a value into a specified data type
• CONVERT() modifies data types and can manage data with different character sets
• FORMAT() formats a numeric value with a specified number of decimal places, a decimal point, and a thousand separator

Comparison functions

In general, the functions of this group can come in handy when you need to compare strings. For example:

• STRCMP() compares two strings and returns 0 if the strings are equal, a negative value if the first string is less than the second one, and a positive value if the first string is greater than the second one
• LIKE() searches for a specified pattern within a string
• SOUNDEX() compares the phonetic similarity of strings

Determine the convertibility of strings to numeric data types

Suppose you want to convert values into numeric data types.

Current values	Desired values
123	1
-123	1
123.321	1
-123.321	1
123.0	1
-123.0	1
0.321	1
0.321	1
123E321	0
ABC	0
“	0
NULL	NULL

You can do it with the help of the REGEXPoperator.

SELECT
  <column_name>,
  <column_name> REGEXP '^+\\-?[0-9]+\\.?[0-9]*$' as is_numeric
FROM
  <table_name>;

Where:

• <column_name> is the name of the column that contains values you want to convert
• <table_name> is the table where the column is located

The output of the query is as follows.

Remove extra spaces in strings

If some values contain unnecessary spaces, as shown in the screenshot, it’s possible to delete them using TRIM() and REPLACE().

This is the query with the operators:

SELECT
  <column_name> AS <column_name_original>,
  REPLACE(REPLACE(REPLACE(TRIM(<column_name>),SPACE(1),'{}'),'}{',SPACE(0)),'{}',SPACE(1)) AS <column_name>
FROM
  <table_name>;

Where:

• <column_name> is the name of the column where data has extra spaces
• <column_name_original> is an alias for <column_name> that will allow referencing the original column with a different name in an output

After you have run the query, you’ll see the following.

Word count determination in strings with a delimiter

In your practice, you’re likely to encounter situations where you need to count words in strings separated by a delimiter. In our example, this delimiter will be a semicolon.

To determine the number of words, it is sufficient to subtract the length of the original string from the length of the string where all delimiter characters are replaced with an empty string, and then add one to the result obtained. Adding one is necessary because the last word in the string is not followed by the delimiter character. Additionally, we should consider the situation when there is an empty string or NULL:

SELECT
  <column_name> AS <column_name>,
  CASE
    WHEN LENGTH(<column_name>) = 0 OR LENGTH(<column_name>) IS NULL THEN 0
    ELSE LENGTH(<column_name>)-LENGTH(REPLACE(<column_name>,';',''))+1
  END AS word_count
FROM
  <table_name>;

Where:

• <column_name> is the name of the column that contains the words you want to count
• <table_name> is the table where the column is located

You’ll obtain the expected result.

Strategies for transforming table rows into lists with delimiters

In case you need to present data from multiple rows in a single field in order to generate reports or export information, then GROUP_CONCAT() and REPLACE() come in handy. For example, there is data in such a format:

1	Letha
1	Wahl
1	Mckee
2	Kraig
3	Harlan
3	Ludwig

And it’s required to modify it so that it looks like this:

1	Letha Wahl Mckee
2	Kraig
3	Harlan Ludwig

The space serves as a separator in this case. Now let’s see how the operators will cope with this task.

SELECT
  <column1>,
  GROUP_CONCAT(<column2> SEPARATOR ' ') AS <column2>
FROM
  <table_name>
GROUP BY
  <column1>;

Where:

• <column1> and <column2> are the names of the columns, which you want to convert into a list
• <table_name> is the table where the columns are placed

Alter delimited lists into table rows

Suppose you need to perform an operation completely opposite to the operation described earlier. And before using the JSON_TABLE() function, which plays a main role in this operation, it’s important to create an extra function that will transform the delimiter-separated list into JSON.

CREATE FUNCTION string_to_json (source_string TEXT, char_separator TEXT) RETURNS json
  NO SQL
BEGIN
  RETURN CONCAT('[\{"name":"', REPLACE(source_string, char_separator, '"}, \{"name":"'), '"}]');
END;

Where:

• source_string is the source list
• char_separator is the separator character

Now let’s convert the obtained JSON into table rows:

SELECT
  <column1>,
  jt.<column2>
FROM
  <table_name>,
  JSON_TABLE( string_to_json(<column2>,' '), '$[*]' COLUMNS(<column2> varchar(255) PATH '$.name')) jt;

Where:

• <column1> and <column2> are the names of the columns, which you want to convert into table rows
• <table_name> is the table where the columns are placed

Capitalize every word in a line

Overall, capitalizing every word can be useful for better data presentation, standardization, and user experience. To solve this task, we’ll utilize the previously created function string_to_json() and the already-known transformation of a delimiter-separated list into table rows.

SELECT
  <table_name>.<column1>,
  jt.<column2> AS <column2>
FROM <table_name>, 
  JSON_TABLE( string_to_json(<table_name>.<column1>,' '),
              '$[*]' COLUMNS(<column2> varchar(255)  PATH '$.name')) jt;

Where:

• <table_name> is the table where the columns are located
• <column1> and <column2> are the names of the columns from which data is retrieved

Then to each row, we’ll add a dummy character $ and convert the first two left characters to UPPER CASE:

SELECT
  <table_name>.<column1>,
  REPLACE(CONCAT('$',jt.<column2>),LEFT(CONCAT('$',jt.<column2>),2),UPPER(LEFT(CONCAT('$',jt.<column2>),2))) AS <column2>
FROM <table_name>, 
  JSON_TABLE( string_to_json(<table_name>.<column2>,' '),
              '$[*]' COLUMNS(<column2> varchar(255)  PATH '$.name')) jt

The query returns records from <column1> from the specified table along with the modified data in <column2>, where the first two characters of each name are in uppercase and the rest of the name remains unchanged.

Now let’s delete $and we’ll get the following query:

SELECT
  <table_name>.<column1>,
    REPLACE(REPLACE(CONCAT('$',jt.<column2>),LEFT(CONCAT('$',jt.<column2>),2),UPPER(LEFT(CONCAT('$',jt.<column2>),2))),'$','') AS <column2>
FROM <table_name>, 
  JSON_TABLE( string_to_json(<table_name>.<column2>,' '),
              '$[*]' COLUMNS(<column2> varchar(255)  PATH '$.name')) jt

Finally, we’ll apply the already-known operation of converting table strings into a list with separators.

SELECT
  <table_name>.<column1>,
  GROUP_CONCAT(
    REPLACE(REPLACE(CONCAT('$',jt.<column2>),LEFT(CONCAT('$',jt.<column2>),2),UPPER(LEFT(CONCAT('$',jt.<column2>),2))),'$','')
    SEPARATOR ' ') AS <column2>
FROM <table_name>, 
  JSON_TABLE( string_to_json(<table_name>.<column2>,' '),
              '$[*]' COLUMNS(<column2> varchar(255)  PATH '$.name')) jt
GROUP BY
  <table_name>.<column1>;

Extract the nth word from a string

To obtain, for example, the third word from a string, the combination of JSON_TABLE() <column> FOR ORDINALITY will be applied. Also, we can’t do without string_to_json() in any way. So the query looks as follows:

SELECT
  <column2>,
  dg.custom_word
FROM <table_name> dl
  LEFT OUTER JOIN ( SELECT
                      <column1>,
                      jt.word_num,
                      jt.custom_word
                    FROM
                      <table_name>, 
                      JSON_TABLE( string_to_json(d.<column2>,' '),
                                  '$[*]' COLUMNS( word_num FOR ORDINALITY, custom_word VARCHAR(255) PATH '$.name')) jt
                    WHERE jt.word_num = <word_number>) dg
    ON dl.<column1> = dg.<column1>;

Where:

• <table_name> is the table where the columns are stored
• <word_number> is the number of the word you want to output
• <column1> is the name of the column that serves for joining the tables and ensuring correct associations between data and a custom word
• <column2> is the name of the column that contains the word you want to receive

Randomize character order in strings

If you have ever thought about whether it’s possible to mix symbols in strings, the answer is “yes”. Even such an operation can be executed in MySQL.

To complete this challenge, we’ll need an extra table called sequence that contains a number of lines, not less than the number of characters in the longest line. In the first stage, we’re going to represent each line as a sequence of characters – one character per row in the table.

WITH RECURSIVE sequence AS (
  SELECT 1 AS rec_id
  UNION ALL
  SELECT rec_id + 1 AS rec_id
  FROM sequence
  WHERE rec_id < 100
  )

SELECT
  <table_name>.<column1>,
  LEFT(SUBSTRING(<table_name>.<column1>,s.rec_id),1) AS resorted_name
FROM
  <table_name>,
  sequence s
WHERE
  s.rec_id <= LENGTH(<table_name>.<column1>);

Where:

• <table_name> is the table where the columns are stored
• <column1> is the name of the column you want to get data from

Finally, we’ll assemble the characters back into a string, but this time in random order:

WITH RECURSIVE sequence AS (
  SELECT 1 AS rec_id
  UNION ALL
  SELECT rec_id + 1 AS rec_id
  FROM sequence
  WHERE rec_id < 100
  )

SELECT
  <table_name>.<column1>,
  GROUP_CONCAT(LEFT(SUBSTRING(<table_name>.<column1>,s.rec_id),1) ORDER BY RAND() SEPARATOR '') AS resorted_name
FROM
  <table_name>,
  sequence s
WHERE
  s.rec_id <= LENGTH(<table_name>.<column1>)
GROUP BY
  <table_name>.<column1>;

Convert delimited lists to the WHERE <column_name> IN (…) clause

SQL WHERE <column_name> IN (...) in MySQL is used to filter query results based on a list of specified values for a particular column. The clause works in the following way:

• <column_name> is the name of the required column
• IN (...) is a conditional operator that checks whether the value of <column_name> matches any value in the specified list

Let’s explore how we can apply the clause in practical scenarios. For example, to obtain all the residents of one city, we need to create a stored procedure named person_list that will take an input parameter p_city_id of type INT. Then the query looks as follows:

CREATE PROCEDURE person_list(p_city_id INT)
BEGIN
  SELECT
    p.person_name,
    p.city_id
  FROM
    persons p
  WHERE
    city_id = p_city_id;
END;

In case it’s required to select all the residents of two cities, this combination should be used:

WHERE city_id IN (param1,param2);

And what should be done if the number of cities varies and is not predetermined?

In this case, a list with a delimiter, for example, 1,3, is formed and passed as a parameter. The problem is that MySQL does not understand that WHERE city_id IN ('1,3') should be read as WHERE city_id IN (1,3). Therefore, it’s necessary to convert the delimited string into rows of a table as MySQL recognizes IN (SELECT FROM …)

CREATE PROCEDURE person_list(p_city_id_list VARCHAR(255))
BEGIN
  SELECT
    p.person_id,
    p.person_name,
    p.city_id
  FROM
    persons p
  WHERE city_id IN ( SELECT jt.city_id
                     FROM JSON_TABLE( string_to_json(p_city_id_list,','),
                      '$[*]' COLUMNS(city_id varchar(255) PATH '$.name')) jt);
END;

To get the final result, call the procedure:

CALL person_list('1,3');

Reasons behind utilizing GUI tools for MySQL queries

GUI (Graphical User Interface) tools for MySQL queries offer several advantages that make them preferred choices for many users. Here are some reasons behind their popularity:

• Ease of use: Such tools provide a user-friendly interface with buttons, menus, and forms. Thus, it’s easier for users of different backgrounds and skillsets to interact with databases.
• Visual query building: GUI instruments often deliver graphical query builders that allow users to build complex queries visually without writing SQL code manually.
• Data visualization: Many GUI tools include built-in data visualization capabilities that let users generate charts, graphs, and reports directly from query results.
• Syntax highlighting and autocompletion: GUI tools typically provide features like syntax highlighting and autocompletion, which help users write SQL queries more efficiently and with fewer errors.
• Cross-platform compatibility: These tools are often cross-platform. It means that they can run on different operating systems such as Windows, macOS, and Linux.
• Security features: They often include connection encryption, SSH tunneling, and user authentication mechanisms.

Key query editing features of dbForge Studio for MySQL

dbForge Studio for MySQL has a wide range of features for simplifying daily work with MySQL and MariaDB databases and enhancing personal productivity. We would like to focus on several features that make this tool powerful and universal.

MySQL code completion

Every time you type a query, dbForge Studio offers context-aware suggestions for keywords, table names, column names, and functions. It’s very convenient and saves you lots of time.

MySQL Code Editor

The Studio has a built-in Code Editor for writing, editing, and running SQL queries. The Editor includes syntax highlighting, code folding, and multiple tabs support.

SQL code formatting

In dbForge Studio, you can automatically format your SQL code according to custom formatting rules.

Instant syntax check

This feature highlights syntax errors while you are typing. It helps identify typos and fix them quickly.

MySQL Debugger

MySQL Debugger is designed to fine-tune stored procedures and functions within the IDE. It provides step-by-step execution, breakpoints, variable inspection, and call stack visualization.

Query Profiler

Query Profiler enables the analysis and optimization of SQL queries. Using the tool, you can get information about query execution times, resource consumption, and query execution plans. This is crucial when there is a need to find bottlenecks and improve query performance.

Encryption

dbForge Studio for MySQL offer various methods of encrypting data and connections. With the encryption features, you can significantly enhance the security and privacy of your MySQL databases and be sure that sensitive information is well-protected.

Conclusion

As you can see, MySQL string functions are an effective means for manipulating and extracting data from strings within databases. The provided examples have demonstrated how these functions are flexible and helpful in various data processing tasks. Throughout our journey into the world of string functions, we’re with a reliable guide – dbForge Studio for MySQL. You can go on enriching your database management experience with this tool. Download dbForge Studio for MySQL for a free trial and allow it to become your indispensable assistant!