Remove or Trim First or Last Few Characters in MySQL Database with SQL
Another useful string function in MySQL database is TRIM() which will return a text string after removing the matching leading or trailing characters, also known as prefixes or suffixes. It’s been described by MySQL reference as function that returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.
Syntax of TRIM():
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
For example:
SELECT TRIM(’ bar ‘);
will return ‘bar’ (spaces dropped).
SELECT TRIM(LEADING ‘x’ FROM ‘xxxbarxxx’);
will return ‘barxxx’ (only leading x characters is removed).
SELECT TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’);
will return ‘bar’ (leading and trailing xs is dropped).
SELECT TRIM(TRAILING ‘xyz’ FROM ‘barxxyz’);
will return ‘barx’ (trailing xyz is cleared).
This function is multi-byte safe. And it can also be used with other SQL command such as UPDATE to perform modification update directly on database table data with SQL statements using tool such as phpMyAdmin.
Share and contribute or get technical support and help at My Digital Life Forums.
Related Articles
- Change or Set MySQL Long Query Time Value for log-slow-queries
- How to Find and Replace Text in MySQL Database using SQL
- MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
- WordPress MySQL SQL Query Error in WPDB Class
- Install Web Server in Windows XP with Apache2, PHP5 and MySQL4 - Part 4
- How to Backup and Restore (Export and Import) MySQL Databases Tutorial
- How to Delete Existing WordPress Post Revisions Stored/Saved
- Enable Logging of Slow Queries (Slow Query Log) in MySQL Database
- Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005
- Delete, Remove or Drop Oracle Stored Packages with Drop Package
Entries (RSS)