CODEWITHSUNDEEP
mysqlsql
Sasha
Sasha

Reputation: 8705

MySQL - find specific number and remove it

I have field which looks like this:

1,17,22,25,111,251,170

I need to find for example number 1 and just number 1, and remove it. Also I need to remove all commas if they exist at the start or end, and double commas to. Is there way to do this within MySQL?

Upvotes: 0

Views: 69

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

For removing preceding and trailing commas you can simply write:-

SELECT REPLACE(LTRIM(RTRIM(REPLACE(COL_NAME, ',', ' '))), ' ', ',');

AND for removing all 1 you can write:-

SELECT REPLACE(COL_NAME, '1', '');

If You Want Merge Them You Can Simply Write:-

SELECT REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(COL_NAME, ',', ' '))), ' ', ',','1',''));

This might be helpful to you.

Upvotes: 2

Related Questions