Using MySQL String Functions

Mahesh Palamuttath October 02, 2022

MySQL can do much more than just store and retrieve data. We can also perform manipulations on the data before retrieving or saving it. That’s where MySQL Functions come in. Functions are simply pieces of code that perform some operations and then return a result. In this post, we will learn a few very useful functions in MySQL such as PAD(Left & Right), TRIM (Leading & trailing), and CONCAT.

Padding is the process of adding some unnecessary material to your strings, often to meet a minimum word count. Padding in MySQL can be very useful in formatting the output of a query. MySQL provides us with two padding functions – LPAD() and RPAD().

MySQL LPAD() is used to left-pad (add padding on the left side) a string with another string, to a specified length.

MySQL RPAD() is used to right-pad (add padding on the right side) a string with another string, to a specified length.

Syntax of MySQL LPAD()

LPAD(string, length, padding_string);

Where

‘string’ is the original string that needs left padding,
‘length’ is the length that the string should have after padding and,
‘padding_string’ is the string that should be either left-padded to ‘string’.

UPDATE table SET cloumn = LPAD(column, 10,'0');

Eg: If you want to append a zero on the LEFT side of a string from the barcode column of the items table, Use the below query

UPDATE items SET barcode = LPAD(barcode, 10,'0');

To exclude specific barcode numbers (1000, 1001, 1002, 1003) from being updated with leading zeros, you can modify the SQL statement by adding a WHERE clause to filter out those values. Here's an example:

UPDATE items

SET barcode = LPAD(barcode, 10, '0')

WHERE barcode NOT IN ('1000', '1001', '1002', '1003');

Syntax of MySQL RPAD()

RPAD(string, length, padding_string);

Where

‘string’ is the original string that needs the right padding,
‘length’ is the length that the string should have after padding, and,
‘padding_string’ is the string that should be right-padded to ‘string’.

UPDATE table SET column = RPAD(column, 10,'0');

Eg: If you want to append a zero on the RIGHT side of the string from the barcode column of the items table, Use the below query

UPDATE items SET barcode = RPAD(barcode, 10,'0');

To update the barcode column with trailing zeros using the RPAD function and exclude specific barcode numbers (1000, 1001, 1002, 1003), you can modify the SQL statement as follows:

UPDATE items

SET barcode = RPAD(barcode, 10, '0')

WHERE barcode NOT IN ('1000', '1001', '1002', '1003');

TRIM () Function

TRIM() function in MySQL is used to clean up data. It is also used to remove the unwanted leading and trailing characters in a string. TRIM() function helps to remove leading/trailing characters.

TRIM Leading

UPDATE table SET column = TRIM(LEADING '0' FROM column)

Eg: If you want to remove the leading zero from the barcode column of the items table, the below syntax will do it

UPDATE items SET barcode = TRIM(LEADING '0' FROM barcode)

TRIM Trailing

UPDATE table SET column = TRIM(TRAILING '0' FROM column)

Eg: If you want to remove trailing zero from the barcode column of the items table, the below syntax will do it

UPDATE items SET barcode = TRIM(TRAILING '0' FROM barcode)

Adding Prefix and Suffix

To add a prefix to the value of a column from a table

UPDATE table SET column = CONCAT('prefix', column)

UPDATE items SET barcode = CONCAT('GEMS', barcode)