List of string functions
More functions than date and time - only the String type. Which in SQL is given by CHAR(n) and VARCHAR(n) types. Let's recall the most popular of them:
| Function | Description | |
|---|---|---|
| 1 | LENGTH(str) | Returns the length of the passed string in bytes |
| 2 | CHAR_LENGTH(str) | Returns the length of the passed string in chars |
| 3 | LOCATE(substr,str), | Searches for a substring in a string similar to the indexOf() method |
| 4 | LOCATE(substr,str,pos) | Searches for a substring in a string starting at pos |
| 5 | CONCAT(str1,str2,...) | Concatenates multiple lines |
| 6 | SUBSTR(), SUBSTRING() | Returns a substring given by a range of characters |
| 7 | LOWER(str) | Converts a string to lowercase |
| 8 | UPPER(str) | Converts a string to uppercase |
| 9 | REPLACE() | Replaces a substring in a string |
| 10 | MATCH() | Checks if a string matches a given pattern |
| eleven | TRIM(str) | Trims empty characters at the beginning and end of a string |
| 12 | LTRIM(str) | Trims empty characters at the beginning of a string |
| 13 | RTRIM(str) | Trims empty characters at the end of a string |
| 14 | TO_BASE64(str) | Converts a string to Base64 |
| 15 | FROM_BASE64(str) | Converts a string from Base64 |
I deliberately grouped the functions into small groups to make it easier to understand how to work with them. Below we will consider one function from each group. You can find a complete list of functions for working with strings in the official documentation .
Let's convert the string
Let's first deal with the simplest functions that simply convert a string to a slightly different form. For example, convert a string to upper and lower case. In general, their behavior is similar to the same functions in the Java language.
So I'll just give a table with a few examples.
| # | Request | Result |
|---|---|---|
| 1 | SELECT LENGTH ('text') | 4 |
| 2 | SELECT LENGTH ('Hello') | 12 |
| 3 | SELECT LOWER ('Hi') | Hello |
| 4 | SELECT UPPER ('Hello') | HELLO |
| 5 | SELECT SUBSTR ('Hello', 2, 3) | riv |
| 6 | SELECT SUBSTR ('Hi, how are you?', 8) | How are you? |
The functions work as expected, just like their counterparts from the JDK.
The only caveat: in the first line, the result is 4, not 8. The thing is that 1 byte (ASCII encoding) is used in the request to encode Latin characters. But if you work with data from the database, then the length of the string will depend on the database encoding settings . You will find many surprises while working with the database :)
Complex actions with strings
Well, let's look at more complex things when working with strings. What would you come up with...
Let's display tasks from the task table, and if the task deadline has already passed, then add the word EXPIRED to the task description!
Sounds interesting. Although we have not yet learned complex conditions, so let's simplify the task a little. Let's just write a query that will display a list of past tasks, but be sure to add the word “EXPIRED!” to the title. .
To do this, we will have to use the CONCAT function:
SELECT CONCAT( 'EXPIRED! ', name) FROM task
WHERE deadline < CURDATE()
The result of this query will be:
| concat('EXPIRED! ', name) |
|---|
| EXPIRED! Fix a bug on the frontend |
Recommendation. If you just need to convert the data to a slightly different form, then this can also be done at the Java code level. But if you want to use SQL server-side string functions (inside WHERE), then you definitely cannot do without them.
GO TO FULL VERSION