Replace repeating special characters in a String - MySQL

user_14237940 0
Viewed 555

How can I replace n/ repeating characters with a single character? All special characters !@#$%^&*()_-+=[]{};:'",<.>/?\|~ I need them to only appear once. I started embedding "replace"s but that got out of hand and required me to have several replaces per character.

String Examples:

  • This is great!!!!!!!!!!!
  • ''''''look at this!!!!!'''''
  • $$$$$$$Money$$$$$$$
  • ,,,,,,,,,come on''''''''''''

Desired Output:

  • This is great!
  • 'Look at this!'
  • $Money$
  • ,come on'
mysql regex
2 Answers

Let's try the following:

([&*()_\-+={}[\];:!@#$%^'",<.>\/?\\|~])+$1

PS: First you should retrieve the desired data with an appropriate query in SQL the standard format of query is as follows:

SELECT <col-name> FROM <table>

Then you have to update it and send Update query to the database.

For more information see the following links:

These question may have more than one solution.

user_73100770

With the suggestions from the guys above I came up with this one:

SELECT REGEXP_REPLACE('''''''look at this!!!!!''''', '([$!&*_;:@#+\'=%^,<.>/?|~])\\1+', '$1');

As mentioned beforehand, it captures each occurrence in the first group and replaces with a single character. Tested on v8.0 and seems to work for your examples.

user_70900570
Related