I have seen many people reading data from MySQL, creating an array or a tuple (recordset) inside the programming language, and only then using the string manipulation functions available in that language. Whoever is using pure C is basically doomed, right?
Well then, let’s review some string functions implemented inside MySQL that can be a lifesaver for every developer, many of which are unavailable in some languages:
CHAR: Returns a character contained in the ASCII table according to the given value. For those who don’t know, there is a numbered table from 1 to 255, a matrix, that determines all valid characters. For example, A = 65, B = 66, and so on.
sqlCopyEditmysql> SELECT CHAR(65);
A
mysql> SELECT CHAR(65,76,69,88);
ALEX
CHAR_LENGTH: Returns the number of characters in a string.
sqlCopyEditmysql> SELECT CHAR_LENGTH('Melissa');
7
CONCAT: Concatenates multiple character sequences (strings) into one:
sqlCopyEditmysql> SET @username = 'Vinicius';
mysql> SELECT CONCAT('Bom Dia ', @username, ', ', 'como você vai?');
Bom Dia Vinicius, como você vai?
CONCAT_WS: Similar to CONCAT, however, the first string is used as a separator and repeated between the other strings.
sqlCopyEditmysql> SELECT CONCAT_WS('/', '31', '12', '2012');
31/12/2012
mysql> SELECT CONCAT_WS('-', 'cod', 'prod', '001');
cod-prod-001
INSTR: Searches for the occurrence of a substring within a string. Returns zero if nothing is found, otherwise returns the starting position (within the string) if a match is found.
sqlCopyEditmysql> SELECT INSTR('Impressora HP Deskjet D1560', 'D1561');
0
mysql> SELECT INSTR('Impressora HP Deskjet D1560', 'D1560');
23
LCASE or LOWER: Converts all uppercase characters to lowercase.
sqlCopyEditmysql> SELECT LCASE('LIMÃO');
limão
mysql> SELECT LOWER('MORANGO');
morango
mysql> SELECT LCASE('TIMÃO');
timinho -- Here the entire MySQL AI intelligence is demonstrated, sorry, couldn’t resist! Sorry Théo!!
LEFT: Returns the leftmost N characters.
sqlCopyEditmysql> SELECT LEFT('Piracicaba', 4);
Pira
LENGTH: Oops. Now things get serious LENGTH returns the number of bytes in a character sequence. It’s impossible not to compare it with CHAR_LENGTH, which returns the number of characters. So we have: LENGTH returns the number of bytes of a string, and CHAR_LENGTH always returns the number of characters. Is this a problem? Yes, depending on the CHARACTER SET in use (LATIN1, UTF8, etc.), a character may cost 1 or more bytes. Let’s make this explanation more interesting.
LATIN1: Using this CHARSET (just for insiders), the letter “ã” (a with tilde) costs exactly 1 byte. In UTF-8, the same letter costs 2 bytes.
Let’s take my beloved São Paulo as an example. São Paulo has 9 characters including the space. However, it costs 9 bytes in Latin1, and 10 bytes in UTF-8. To prove this, we need to use the CONVERT function which guarantees, forces, the use of a CHARSET.
sqlCopyEditmysql> SELECT CHAR_LENGTH(CONVERT('São Paulo' USING UTF8));
9
mysql> SELECT CHAR_LENGTH(CONVERT('São Paulo' USING LATIN1));
9
CONVERT('STRING' USING <CHARSET>) — basically this is the use of the CONVERT function, for which I will write a dedicated post on CHARSET and CONVERT. “The professor (how is he) is getting old.” Talking about LENGTH gave an example of CHAR_LENGTH. No, little grasshopper, the goal here is to show that CHAR_LENGTH returns the number of characters regardless of the CHARSET in use.
Now, back to LENGTH:
sqlCopyEditmysql> SELECT LENGTH(CONVERT('São Paulo' USING LATIN1));
9
mysql> SELECT LENGTH(CONVERT('São Paulo' USING UTF8));
10
LPAD/RPAD: Fills with characters on the left (LPAD) or right (RPAD) up to the specified length:
sqlCopyEditmysql> SELECT LPAD('500', 6, '0');
000500
Where ‘500’ is the string, 6 is the length limit, and ‘0’ is the character to be used for padding.
sqlCopyEditmysql> SELECT RPAD('ALE', 9, '?');
ALE??????
LTRIM/TRIM/RTRIM: Space removers. (L) left side, (R) right side, while TRIM removes spaces from both left and right but never spaces between characters.
sqlCopyEditmysql> SELECT LTRIM(' Rio de Janeiro');
Rio de Janeiro
mysql> SELECT RTRIM('Rio de Janeiro ');
Rio de Janeiro
mysql> SELECT CONCAT('[', TRIM(' Rio de Janeiro '), ']');
[Rio de Janeiro]
MID/SUBSTRING/SUBSTR: Allows extracting parts of a character sequence.
sqlCopyEditmysql> SELECT MID('Rio de Janeiro', 5, 2);
de
Where ‘Rio de Janeiro’ is the string, 5 is the first position inside the string (starting from 1), and 2 characters should be extracted.
sqlCopyEditmysql> SELECT SUBSTRING('MySQL é o melhor banco de dados', 11, 6);
melhor
REPEAT: Simply repeats a character or string N times:
sqlCopyEditSELECT REPEAT('*', 10);
**********
SELECT REPEAT('Sim ', 3);
Sim Sim Sim
REPLACE: Replaces a character or sequence inside a string.
sqlCopyEditmysql> SELECT REPLACE('www.mysql.com', '.com', '.com.br');
www.mysql.com.br
Where ‘www.mysql.com‘ is the string to modify, ‘.com’ is what should be found and replaced by ‘.com.br’.
RIGHT: Returns the rightmost N characters of a string.
sqlCopyEditmysql> SELECT RIGHT('ALEXANDRE', 5);
ANDRE
UCASE/UPPER: Converts lowercase letters to uppercase.
sqlCopyEditmysql> SELECT UCASE('melissa');
MELISSA
mysql> SELECT UPPER('théo');
THÉO
mysql> SELECT UCASE('corinthians');
SÃO PAULO FUTEBOL CLUBE
REVERSE: Reverses or inverts a string.
sqlCopyEditmysql> SELECT REVERSE('1234');
4321
Folks, basically, here are listed 90% of MySQL’s string manipulation functions. Obviously, all of them can be nested inside each other without any distinction. String manipulation functions are especially useful within SQL DML commands, such as INSERT, UPDATE, DELETE, REPLACE, and especially SELECT statements. All these functions can also be used in MySQL embedded programs known as PROCS or STORED ROUTINES, such as events, functions, procedures, and triggers.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
