How to selectively remove a particular value from a column which contains more than one value each separated...
for example, consider a table like below (table name=accounts)
account owner_ids
PA account sa123,rt458,hf678,de348f
RA account gg678n,fk980,rt458,hf678
Here, I want to remove owner_ids "rt458" and "hf678" from the entire table and after removing the commas should be perfect (I mean after removing also the remaining owner_ids should be separated by comma as before and there should not be any unnecessary comma in the column)
Any kind of help is appreciated, Help me out guys
mysql sql database oracle plsql
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
asked Nov 11 at 7:05
David
91
add a comment |
for example, consider a table like below (table name=accounts)
account owner_ids
PA account sa123,rt458,hf678,de348f
RA account gg678n,fk980,rt458,hf678
Here, I want to remove owner_ids "rt458" and "hf678" from the entire table and after removing the commas should be perfect (I mean after removing also the remaining owner_ids should be separated by comma as before and there should not be any unnecessary comma in the column)
Any kind of help is appreciated, Help me out guys
mysql sql database oracle plsql
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
asked Nov 11 at 7:05
David
91
Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row.
– Tim Biegeleisen
Nov 11 at 7:33
@vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you.
– mathguy
Nov 11 at 14:31
add a comment |
for example, consider a table like below (table name=accounts)
account owner_ids
PA account sa123,rt458,hf678,de348f
RA account gg678n,fk980,rt458,hf678
Here, I want to remove owner_ids "rt458" and "hf678" from the entire table and after removing the commas should be perfect (I mean after removing also the remaining owner_ids should be separated by comma as before and there should not be any unnecessary comma in the column)
Any kind of help is appreciated, Help me out guys
mysql sql database oracle plsql
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
asked Nov 11 at 7:05
David
91
for example, consider a table like below (table name=accounts)
account owner_ids
PA account sa123,rt458,hf678,de348f
RA account gg678n,fk980,rt458,hf678
Here, I want to remove owner_ids "rt458" and "hf678" from the entire table and after removing the commas should be perfect (I mean after removing also the remaining owner_ids should be separated by comma as before and there should not be any unnecessary comma in the column)
Any kind of help is appreciated, Help me out guys
mysql sql database oracle plsql
mysql sql database oracle plsql
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
asked Nov 11 at 7:05
David
91
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
asked Nov 11 at 7:05
David
91
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
edited Nov 11 at 7:12
krithikaGopalakrisnan
538218
538218
asked Nov 11 at 7:05
David
91
asked Nov 11 at 7:05
David
91
asked Nov 11 at 7:05
David
91
91
Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row.
– Tim Biegeleisen
Nov 11 at 7:33
@vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you.
– mathguy
Nov 11 at 14:31
add a comment |
Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row.
– Tim Biegeleisen
Nov 11 at 7:33
@vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you.
– mathguy
Nov 11 at 14:31
Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row.
– Tim Biegeleisen
Nov 11 at 7:33
Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row.
– Tim Biegeleisen
Nov 11 at 7:33
@vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you.
– mathguy
Nov 11 at 14:31
@vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you.
– mathguy
Nov 11 at 14:31
add a comment |
2 Answers
2
active
oldest
votes
You can use REPLACE function twice:
update accounts
set owner_ids = REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678');
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
Or you can use REGEXP_REPLACE to replace/remove those values by regex:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,rt458||,hf678)', '');
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.
To support case where rt458 is the first entry in the CSV list (As @TimBiegeleisen commented) you will have to allow no comma as:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,{0,1}rt458||,{0,1}hf678)', '');
Using replace you will need to double calls:
update accounts
set owner_ids = REPLACE(REPLACE(REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678', 'rt458,', 'hf678,');
Also you can see other solutions to remove value from comma separated string
answered Nov 11 at 7:16
user7294900
20.4k103258
1
What about the case wherert458is the first entry in the CSV list?
– Tim Biegeleisen
Nov 11 at 7:32
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
What about the case where there is another token,rt4586, which mustn't be removed? You will just removert458from it, leaving the6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.
– mathguy
Nov 11 at 15:46
add a comment |
UPDATE accounts
SET owner_ids = TRIM(BOTH ',' FROM
REPLACE(REPLACE(CONCAT(',', owner_ids, ','), ',rt458,', ''), ',hf678,', '')
);
First prepend and append the field with a comma CONCAT(',', owner_ids, ',') to make it uniform.
Then, remove ,rt458, and ,hf678, from it.
Later remove commas from both at the start and end which we added to make the structure uniform.
This will maintain single commas between the values.
Please note, you should prefer normalizing
owner_idsso that such updates are easy to handle.
answered Nov 11 at 7:28
Samir
5,2312626
What if one of the tokens isrt4586? Your solution will removert458, leaving the6behind (when in fact that token shouldn't be touched in the first place).
– mathguy
Nov 11 at 15:47
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53246573%2fhow-to-selectively-remove-a-particular-value-from-a-column-which-contains-more-t%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use REPLACE function twice:
update accounts
set owner_ids = REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678');
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
Or you can use REGEXP_REPLACE to replace/remove those values by regex:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,rt458||,hf678)', '');
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.
To support case where rt458 is the first entry in the CSV list (As @TimBiegeleisen commented) you will have to allow no comma as:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,{0,1}rt458||,{0,1}hf678)', '');
Using replace you will need to double calls:
update accounts
set owner_ids = REPLACE(REPLACE(REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678', 'rt458,', 'hf678,');
Also you can see other solutions to remove value from comma separated string
answered Nov 11 at 7:16
user7294900
20.4k103258
1
What about the case wherert458is the first entry in the CSV list?
– Tim Biegeleisen
Nov 11 at 7:32
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
What about the case where there is another token,rt4586, which mustn't be removed? You will just removert458from it, leaving the6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.
– mathguy
Nov 11 at 15:46
add a comment |
You can use REPLACE function twice:
update accounts
set owner_ids = REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678');
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
Or you can use REGEXP_REPLACE to replace/remove those values by regex:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,rt458||,hf678)', '');
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.
To support case where rt458 is the first entry in the CSV list (As @TimBiegeleisen commented) you will have to allow no comma as:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,{0,1}rt458||,{0,1}hf678)', '');
Using replace you will need to double calls:
update accounts
set owner_ids = REPLACE(REPLACE(REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678', 'rt458,', 'hf678,');
Also you can see other solutions to remove value from comma separated string
answered Nov 11 at 7:16
user7294900
20.4k103258
1
What about the case wherert458is the first entry in the CSV list?
– Tim Biegeleisen
Nov 11 at 7:32
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
What about the case where there is another token,rt4586, which mustn't be removed? You will just removert458from it, leaving the6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.
– mathguy
Nov 11 at 15:46
add a comment |
You can use REPLACE function twice:
update accounts
set owner_ids = REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678');
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
Or you can use REGEXP_REPLACE to replace/remove those values by regex:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,rt458||,hf678)', '');
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.
To support case where rt458 is the first entry in the CSV list (As @TimBiegeleisen commented) you will have to allow no comma as:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,{0,1}rt458||,{0,1}hf678)', '');
Using replace you will need to double calls:
update accounts
set owner_ids = REPLACE(REPLACE(REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678', 'rt458,', 'hf678,');
Also you can see other solutions to remove value from comma separated string
answered Nov 11 at 7:16
user7294900
20.4k103258
You can use REPLACE function twice:
update accounts
set owner_ids = REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678');
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
Or you can use REGEXP_REPLACE to replace/remove those values by regex:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,rt458||,hf678)', '');
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.
To support case where rt458 is the first entry in the CSV list (As @TimBiegeleisen commented) you will have to allow no comma as:
update accounts
set owner_ids = REGEXP_REPLACE(owner_ids,
'(,{0,1}rt458||,{0,1}hf678)', '');
Using replace you will need to double calls:
update accounts
set owner_ids = REPLACE(REPLACE(REPLACE(REPLACE(owner_ids,
',rt458' ),',hf678', 'rt458,', 'hf678,');
Also you can see other solutions to remove value from comma separated string
answered Nov 11 at 7:16
user7294900
20.4k103258
edited Nov 11 at 7:49
answered Nov 11 at 7:16
user7294900
20.4k103258
answered Nov 11 at 7:16
user7294900
20.4k103258
answered Nov 11 at 7:16
user7294900
20.4k103258
20.4k103258
1
What about the case wherert458is the first entry in the CSV list?
– Tim Biegeleisen
Nov 11 at 7:32
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
What about the case where there is another token,rt4586, which mustn't be removed? You will just removert458from it, leaving the6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.
– mathguy
Nov 11 at 15:46
add a comment |
1
What about the case wherert458is the first entry in the CSV list?
– Tim Biegeleisen
Nov 11 at 7:32
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
What about the case where there is another token,rt4586, which mustn't be removed? You will just removert458from it, leaving the6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.
– mathguy
Nov 11 at 15:46
1
1
What about the case where
rt458 is the first entry in the CSV list?– Tim Biegeleisen
Nov 11 at 7:32
What about the case where
rt458 is the first entry in the CSV list?– Tim Biegeleisen
Nov 11 at 7:32
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
@TimBiegeleisen Thanks for commenting, I updated my answer
– user7294900
Nov 11 at 7:45
What about the case where there is another token,
rt4586, which mustn't be removed? You will just remove rt458 from it, leaving the 6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.– mathguy
Nov 11 at 15:46
What about the case where there is another token,
rt4586, which mustn't be removed? You will just remove rt458 from it, leaving the 6. The problem can be solved with REPLACE, but the solution must be written with much more care than you have.– mathguy
Nov 11 at 15:46
add a comment |
UPDATE accounts
SET owner_ids = TRIM(BOTH ',' FROM
REPLACE(REPLACE(CONCAT(',', owner_ids, ','), ',rt458,', ''), ',hf678,', '')
);
First prepend and append the field with a comma CONCAT(',', owner_ids, ',') to make it uniform.
Then, remove ,rt458, and ,hf678, from it.
Later remove commas from both at the start and end which we added to make the structure uniform.
This will maintain single commas between the values.
Please note, you should prefer normalizing
owner_idsso that such updates are easy to handle.
answered Nov 11 at 7:28
Samir
5,2312626
What if one of the tokens isrt4586? Your solution will removert458, leaving the6behind (when in fact that token shouldn't be touched in the first place).
– mathguy
Nov 11 at 15:47
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
add a comment |
UPDATE accounts
SET owner_ids = TRIM(BOTH ',' FROM
REPLACE(REPLACE(CONCAT(',', owner_ids, ','), ',rt458,', ''), ',hf678,', '')
);
First prepend and append the field with a comma CONCAT(',', owner_ids, ',') to make it uniform.
Then, remove ,rt458, and ,hf678, from it.
Later remove commas from both at the start and end which we added to make the structure uniform.
This will maintain single commas between the values.
Please note, you should prefer normalizing
owner_idsso that such updates are easy to handle.
answered Nov 11 at 7:28
Samir
5,2312626
What if one of the tokens isrt4586? Your solution will removert458, leaving the6behind (when in fact that token shouldn't be touched in the first place).
– mathguy
Nov 11 at 15:47
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
add a comment |
UPDATE accounts
SET owner_ids = TRIM(BOTH ',' FROM
REPLACE(REPLACE(CONCAT(',', owner_ids, ','), ',rt458,', ''), ',hf678,', '')
);
First prepend and append the field with a comma CONCAT(',', owner_ids, ',') to make it uniform.
Then, remove ,rt458, and ,hf678, from it.
Later remove commas from both at the start and end which we added to make the structure uniform.
This will maintain single commas between the values.
Please note, you should prefer normalizing
owner_idsso that such updates are easy to handle.
answered Nov 11 at 7:28
Samir
5,2312626
UPDATE accounts
SET owner_ids = TRIM(BOTH ',' FROM
REPLACE(REPLACE(CONCAT(',', owner_ids, ','), ',rt458,', ''), ',hf678,', '')
);
First prepend and append the field with a comma CONCAT(',', owner_ids, ',') to make it uniform.
Then, remove ,rt458, and ,hf678, from it.
Later remove commas from both at the start and end which we added to make the structure uniform.
This will maintain single commas between the values.
Please note, you should prefer normalizing
owner_idsso that such updates are easy to handle.
answered Nov 11 at 7:28
Samir
5,2312626
edited Nov 12 at 7:35
answered Nov 11 at 7:28
Samir
5,2312626
answered Nov 11 at 7:28
Samir
5,2312626
answered Nov 11 at 7:28
Samir
5,2312626
5,2312626
What if one of the tokens isrt4586? Your solution will removert458, leaving the6behind (when in fact that token shouldn't be touched in the first place).
– mathguy
Nov 11 at 15:47
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
add a comment |
What if one of the tokens isrt4586? Your solution will removert458, leaving the6behind (when in fact that token shouldn't be touched in the first place).
– mathguy
Nov 11 at 15:47
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
What if one of the tokens is
rt4586? Your solution will remove rt458, leaving the 6 behind (when in fact that token shouldn't be touched in the first place).– mathguy
Nov 11 at 15:47
What if one of the tokens is
rt4586? Your solution will remove rt458, leaving the 6 behind (when in fact that token shouldn't be touched in the first place).– mathguy
Nov 11 at 15:47
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
Oh yes, you're right. Updated my answer. Thank you for pointing it out.
– Samir
Nov 12 at 7:36
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53246573%2fhow-to-selectively-remove-a-particular-value-from-a-column-which-contains-more-t%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row.
– Tim Biegeleisen
Nov 11 at 7:33
@vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you.
– mathguy
Nov 11 at 14:31