LINUX, FOSS AND LIBRARY TECHNOLOGY ENTHUSIAST

Sunday, October 2, 2022

Using MySQL String Functions

0 comments

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');

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');

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

To remove prefix

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

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

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

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

To remove suffix

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

Reference:

No comments:

Post a Comment