In MySQL, you can convert a string to its hexadecimal representation by using the HEX() function.

The HEX() function works on both strings and numbers, however, its handling of each of these is slightly different. When used to convert a number, it returns a hexadecimal string representation of the number treated as a longlong (BIGINT) number. But when converting a string, it returns a hexadecimal string representation of the string where each byte of each character is converted to two hexadecimal digits.

Continue reading →

MySQL includes various functions that can assist you when working with hexadecimal values. One of the most obvious functions is the HEX() function, which enables you to convert a string into its hexadecimal equivalent.

However, there may be times where you want to convert a hexadecimal string back to a more readable string. That’s what this article is about.

Here are three ways to “unhex” a string in MySQL:

• The UNHEX() function
• The X hexadecimal notation
• The 0x notation

Below are examples of each of these methods.

Continue reading →

One of the string functions in MySQL is STRCMP().  This function allows you to compare two strings according to their sort order.

The function accepts two arguments. Each argument is the string to compare. It returns either 1, -1, or 0, depending on whether the first string is larger, smaller, or the same size as the second string, according to sort order.

Continue reading →

Sometimes you need to pad a string with spaces. Or perhaps you’ll pad it with another character. Sometimes you need to pad it on the left. Other times you need to pad it on the right. Or maybe you need to pad it on both sides.

All of the above can be done in MySQL using the LPAD() and/or RPAD() string functions.

Continue reading →

When working with MySQL, you might occasionally encounter situations where you need to pad a bunch of numbers with leading zeros.

Perhaps you have a requirement that all numbers have three digits, but in the data you’ve been supplied, the numbers range from a single digit, to two, and maybe three digits. Your requirement might be to pad all numbers with leading zeros, but only to make up for any shortfall in the three digit requirement.

The LPAD() function does exactly what you want in this scenario.

Continue reading →

This article demonstrates how to repeat a string multiple times in MySQL using the REPEAT() function.

As the name suggests, the REPEAT() function can be used to repeat a string. More specifically, it allows you to specify how many times the string should be repeated.

Continue reading →