Remove All Backslash into string in MySQL query

Question

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 : )

Are you sure the backslashes are really in the string and need to be removed? That looks like the syntax for writing a string that contains quotes, by escaping the embedded quotes. — Barmar, Dec 14 '22 at 18:12
yes, beacuse i want to add in where in clause like this SELECT * FROM kapp_staging.kols where `kol_id` in (REPLACE('160f7a4a-766a-4c23-a155-8bd3f7389f77\',\'63233bfc-b663-4c73-890b-00a48d79c4dc','\'',"'")); — Balwant, Dec 14 '22 at 18:16
There are no backslashes in the query you're trying to perform. — Barmar, Dec 14 '22 at 18:27
Please add sample contents of the `kols` and `news_items` tables and the expected results. — Barmar, Dec 14 '22 at 18:31
It shouldn't be `IN REPLACE((select...))` it should be `IN (SELECT REPLACE(...))` — Barmar, Dec 14 '22 at 18:32
I'm getting tired of trying to guess what you're doing. Please add sample data like I asked. — Barmar, Dec 14 '22 at 18:38
@Balwant: Please fix this [DBFIDDLE](https://dbfiddle.uk/9BeLxMcz) to make clear how your data looks like. (and post the new link to the changed fiddle) — Luuk, Dec 14 '22 at 19:00

Barmar · Accepted Answer · 2022-12-14T20:29:57.313

2

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.

edited Dec 14 '22 at 20:29

answered Dec 14 '22 at 18:15

Barmar

741,623
53
500
612

yes, i tries this solutions but i want to do in mysql not in php. i have to perform this query but \ is in my string so query dose't work SELECT * FROM kapp_staging.kols where kol_id in (REPLACE('160f7a4a-766a-4c23-a155-8bd3f7389f77\',\'63233bfc-b663-4c73-890b-00a48d79c4dc','\'',"'")); – Balwant Dec 14 '22 at 18:19
My first code snippet is MySQL without PHP. – Barmar Dec 14 '22 at 18:22
Do you only want to remove the backslash before `'`? The question says you want to remove all backslashes, that's what my answer does. – Barmar Dec 14 '22 at 18:23
SELECT * FROM kapp_staging.kols where `kol_id` in (REPLACE('160f7a4a-766a-4c23-a155-8bd3f7389f77\',\'63233bfc-b663-4c73-890b-00a48d79c4dc','\\'','')); but did't work – Balwant Dec 14 '22 at 18:24
There's no backslash in that string, so there's nothing to remove. The backslash is the escape character needed to allow the embedded quote. – Barmar Dec 14 '22 at 18:25
this is not literal i select from another table via sub query. – Balwant Dec 14 '22 at 18:25
Please update the question, show the table contents and the query. – Barmar Dec 14 '22 at 18:26
@Balwant I've updated my answer to show how to do it with your data, and added to your dbfiddle to demonstrate it. – Barmar Dec 14 '22 at 20:35

Remove All Backslash into string in MySQL query

1 Answers1