Remove All Backslash into string in MySQL query-CodePudding

I have this type of string

'160f7a4a-766a-4c23-a155-8bd3f7389f77\', \'63233bfc-b663-4c73-890b-00a48d79c4dc'

In one column and I want like

'160f7a4a-766a-4c23-a155-8bd3f7389f77','63233bfc-b663-4c73-890b-00a48d79c4dc'

This type of result in MySQL

i have to perform query like

SELECT * FROM kapp_staging.kols where `kol_id` in (select REPLACE(json_id,'\'',"'") FROM kapp_staging.news_items 
 where `id` = '991'))

in where in clause i have subquery and in subquery i geting

'160f7a4a-766a-4c23-a155-8bd3f7389f77\', \'63233bfc-b663-4c73-890b-00a48d79c4dc'

this type of value so i need to remove \ from value so my where in query work fine.

i have data like:

Kols table
| id | kol_id                                   | name    | data  |
|----|----------------------------------------  |---------| ------|
| 1  |160f7a4a-766a-4c23-a155-8bd3f7389f77      | balwant | data  |
| 2  |63233bfc-b663-4c73-890b-00a48d79c4dc      | vikram  | data  |

news items
| id | json_id | data    |
|----|-----------------------------------------------------------------------------------------|---------|
| 991  | {'\160f7a4a-766a-4c23-a155-8bd3f7389f77\','\160f7a4a-766a-4c23-a155-8bd3f7389f77\'} | data    |

I tried many ways but didn't get this response.

Thanks in Advance : )

CodePudding user response：

The backslashes aren't in the data, they're just used to escape the quotes when inserting into the table. So you don't need to remove them.

However, you can't use IN to match values in a comma-delimited list, you need to use FIND_IN_SET(); see Search with comma-separated value mysql

You also need to remove the quotes and curly braces before you can use FIND_IN_SET().

SELECT DISTINCT k.*
FROM kols AS k
JOIN news_items AS n 
    ON FIND_IN_SET(k.kol_id, 
        REPLACE(REPLACE(REPLACE(json_id, '{', ''), '}', ''), "'", ''))

DEMO

Things would be much easier if you normalized your data and put the list of IDs into a separate table with one row per ID.