Search code examples
mysqlregexregexp-replace

How to remove all selected character from any strings which is not at first position

How can I remove all the "A" from any character string (except of the "A" located at the beginning position of same string) through the regular expression in MySQL ?

For example I have a column "MemberName" containing string "ANSHUMAN" and many more names like that. I want to know how can I remove all the "A" except of "A" situated at starting position from string "ANSHUMAN".

Solution

  • You can use the following solution using REGEXP_REPLACE:

    SELECT REGEXP_REPLACE(column_name, '[A]+', '', 2)
FROM table_name

    The fourth parameter of REGEXP_REPLACE defines the starting position of the search.

    Another solution without using regular expression (in case MySQL version doesn't support):

    SELECT CONCAT(LEFT(column_name, 1), REPLACE(SUBSTR(column_name, 2), 'A', ''))
FROM table_name

    demo on dbfiddle.uk