I have a string in a column and want to replace ROLE_ADMIN roles from the column below is running successfully "ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC,ROLE_ADMIN" using replace

    **select REPLACE('ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC,ROLE_ADMIN -COPY',',ROLE_ADMIN','')**

However there is a usecase as below here I want data should be unchanged as ROLE_ADMIN -COPY is a different role "ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC,ROLE_ADMIN -COPY" but its printing ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC -COPY

Can anyone help in tackling this ? Note:- I can use only one generic query

CodePudding user response：

You can surround the list with the delimiter characters and then replace the term and the leading and trailing delimiters with a single delimiter to ensure you match an entire term and then trim the string to remove the surrounding delimiters.

In Oracle, that would be:

SELECT TRIM(
         BOTH ',' FROM
         REPLACE(
           ','||role_list||',',
           ',ROLE_ADMIN,',
           ','
         )
       ) AS updated_list
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (role_list) AS
SELECT 'ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC,ROLE_ADMIN' FROM DUAL UNION ALL
SELECT 'ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC,ROLE_ADMIN_COPY' FROM DUAL UNION ALL
SELECT 'ROLE_DEVELOPER,ROLE_PRV1,ROLE_TEST,ROLE_VISITOR,ROLE_DOC,ROLE_ADMIN,ROLE_ADMIN_COPY' FROM DUAL;

Outputs:

Oracle fiddle

In MySQL, you need to change to use CONCAT rather than ||:

SELECT TRIM(
         BOTH ',' FROM
         REPLACE(
           CONCAT(',',role_list,','),
           ',ROLE_ADMIN,',
           ','
         )
       ) AS updated_list
FROM   table_name;

MySQL fiddle