MYSQL TRIM() and REPLACE()

Using TRIM() and REPLACE() syntax will help you to get rid off of extra space, tabs, and new line in your database records. These two are useful in cleaning up your records for accurate QUERY especially when using WHERE conditions.

Using TRIM() - function returns a string after removing all prefixes or suffixes from the given string.

Syntax

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str)

Examples

TRIM(' test');

Result: test

TRIM(LEADING 'test' FROM 'testleading'); 

Result: leading

TRIM(TRAILING 'test' FROM 'leadingtest'); 

Result: leading

TRIM(BOTH 'test' FROM 'testleadingtest'); 

Result: leading As you can see, TRIM() only remove extra white space in the prefix, leanding and trailing text but not text/character in between. That's where REPLACE() took the spot. MySQL REPLACE() replaces all the occurrences of a substring within a string.

Syntax

REPLACE(str, find_string, replace_with)

Example:

REPLACE('webdosh','os','o');

Result: webdoh