An example of MySQL code that executes TRIM() to remove a prefix and/or a suffix from all entries that match a WHERE clause

 in Programming / SQL  tagged MariaDB / mysql / prefix / suffix / trim by

The following code will remove the prefix http://wow.example.com from all rows that match the where clause:

1
2
3
4
5
6
Update `my_table`
set
`my_column` = TRIM(
  LEADING 'http://wow.example.com'
  FROM `my_column`)
WHERE (`my_column` LIKE '%http://wow.example.com/%');

The next block will remove the suffix index.php from all entries that match the where clause:

1
2
3
4
5
6
Update `my_table`
set
`my_column` = TRIM(
  TRAILING 'index.php'
  FROM `my_column`)
WHERE (`my_column` LIKE '%/index.php%');

In case we need to remove the string needle both from the prefix and the suffix while using a where clause, we can use the following code:

1
2
3
4
5
6
Update `my_table`
set
`my_column` = TRIM(
  BOTH 'needle'
  FROM `my_column`)
WHERE (`my_column` LIKE '%needle%');

This post is also available in: Greek

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.