Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: How to replace any instances of 'NT NT' at the beginning of a column string with 'NT'?

I need to update the display_name column in all rows of my form table to replace any occurrences of ‘NT NT’ at the beginning of the display name with a single ‘NT’. How do I do that given I need it to search for ‘NT NT%’ instead of the exact string ‘NT NT’?

Tried trim and replace functions, but running into issues with the variable %

Tags:

2

Answers


  1. 0

    You don’t need to use % during the replacement, only when searching for the matches.

    UPDATE yourTable
SET display_name = REPLACE(display_name, 'NT NT', 'NT')
WHERE display_name LIKE 'NT NT%'

    Note that this wont work if the matching strings have multiple NT NT in them, since it will replace all of them. If that’s a problem, you can use REGEXP_REPLACE() to replace just the first occurrence:

    UPDATE yourTable
SET display_name = REGEXP_REPLACE(display_name, '^NT NT', 'NT')
WHERE display_name LIKE 'NT NT%'
    Login or Signup to reply.
  2. 0

    First answer is correct in all aspect.

    As, you tried with TRIM function.
    So, there is an another alternative to do with TRIM function.

    set @st = 'NT NT hello world';
select  REGEXP_REPLACE(TRIM(@st), '^NT', '') AS VALUE;

    enter image description here

    Use Position, occurrence & match_type if required for ignore case.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search