Search code examples
mysqlsqlstringtrim

Trim different characters from beginning and end of string in MySQL

In a database running MySQL I have data in a column that contains text that begins and ends with a paragraph tag and I am trying to remove them during the select statement

<p>Some Text</p>

I understand the TRIM statement can remove specific characters from the start, end or both of the string so

TRIM('x' FROM xSometextx) as trimmed

This will remove the x from the beginning and end of the string, but I cannot find away to TRIM the beginning and end characters if they are different. For example is it possible to remove the leading <p> and trailing </p> in the select statement

Solution

  • You can use a nested TRIM with the specifier LEADING and TRAILING to remove the <p> tag:

    SELECT TRIM(TRAILING '</p>' FROM TRIM(LEADING '<p>' FROM '<p>Test</p>'))

    Since MySQL 8.0 you can also use REGEXP_REPLACE to remove the <p> tag, like the TRIM above:

    SELECT REGEXP_REPLACE('<p>Test</p>', '^\<p\>|\<\/p\>$', '')

    demo on dbfiddle.uk