Remove all instances of specific value from comma separated string
2
I want to remove a given value (e.g. 1) from the following string without splitting or using XML functionality.
/* input: */ @ObjectValue = '1,121,4,5,1,111,131,1'
/* output: */ '121,4,5,111,131'
sql
sql-server string csv tsql edited Nov 14 '18 at 6:36
Salman A
178k66338428
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
|
show 3 more comments
2
I want to remove a given value (e.g. 1) from the following string without splitting or using XML functionality.
/* input: */ @ObjectValue = '1,121,4,5,1,111,131,1'
/* output: */ '121,4,5,111,131'
sql
sql-server string csv tsql edited Nov 14 '18 at 6:36
Salman A
178k66338428
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
1
what is the condition ? Always the first and last ? Or Always1? You need to specify the rules and condition
– Squirrel
Nov 13 '18 at 11:07
1
This is (one of the) reason(s) you should never store cvs data. Normalize your data, and you'll never have this problem.
– HoneyBadger
Nov 13 '18 at 11:09
This is going to be one ugly query. I recommend avoiding using CSV in this manner. If you bring your data into SQL Server, you should normalize it.
– Tim Biegeleisen
Nov 13 '18 at 11:11
Which sql server version are you using?
– Salman A
Nov 13 '18 at 11:30
All '1' whether at the start,middle or end
– Deep patel
Nov 13 '18 at 11:53
|
show 3 more comments
2
2
2
I want to remove a given value (e.g. 1) from the following string without splitting or using XML functionality.
/* input: */ @ObjectValue = '1,121,4,5,1,111,131,1'
/* output: */ '121,4,5,111,131'
sql
sql-server string csv tsql edited Nov 14 '18 at 6:36
Salman A
178k66338428
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
I want to remove a given value (e.g. 1) from the following string without splitting or using XML functionality.
/* input: */ @ObjectValue = '1,121,4,5,1,111,131,1'
/* output: */ '121,4,5,111,131'
sql
sql-server string csv tsql sql
sql-server string csv tsql edited Nov 14 '18 at 6:36
Salman A
178k66338428
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
edited Nov 14 '18 at 6:36
Salman A
178k66338428
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
edited Nov 14 '18 at 6:36
Salman A
178k66338428
edited Nov 14 '18 at 6:36
Salman A
178k66338428
edited Nov 14 '18 at 6:36
Salman A
178k66338428
178k66338428
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
asked Nov 13 '18 at 11:05
Deep patelDeep patel
1216
1216
1
what is the condition ? Always the first and last ? Or Always1? You need to specify the rules and condition
– Squirrel
Nov 13 '18 at 11:07
1
This is (one of the) reason(s) you should never store cvs data. Normalize your data, and you'll never have this problem.
– HoneyBadger
Nov 13 '18 at 11:09
This is going to be one ugly query. I recommend avoiding using CSV in this manner. If you bring your data into SQL Server, you should normalize it.
– Tim Biegeleisen
Nov 13 '18 at 11:11
Which sql server version are you using?
– Salman A
Nov 13 '18 at 11:30
All '1' whether at the start,middle or end
– Deep patel
Nov 13 '18 at 11:53
|
show 3 more comments
1
what is the condition ? Always the first and last ? Or Always1? You need to specify the rules and condition
– Squirrel
Nov 13 '18 at 11:07
1
This is (one of the) reason(s) you should never store cvs data. Normalize your data, and you'll never have this problem.
– HoneyBadger
Nov 13 '18 at 11:09
This is going to be one ugly query. I recommend avoiding using CSV in this manner. If you bring your data into SQL Server, you should normalize it.
– Tim Biegeleisen
Nov 13 '18 at 11:11
Which sql server version are you using?
– Salman A
Nov 13 '18 at 11:30
All '1' whether at the start,middle or end
– Deep patel
Nov 13 '18 at 11:53
1
1
what is the condition ? Always the first and last ? Or Always
1 ? You need to specify the rules and condition– Squirrel
Nov 13 '18 at 11:07
what is the condition ? Always the first and last ? Or Always
1 ? You need to specify the rules and condition– Squirrel
Nov 13 '18 at 11:07
1
1
This is (one of the) reason(s) you should never store cvs data. Normalize your data, and you'll never have this problem.
– HoneyBadger
Nov 13 '18 at 11:09
This is (one of the) reason(s) you should never store cvs data. Normalize your data, and you'll never have this problem.
– HoneyBadger
Nov 13 '18 at 11:09
This is going to be one ugly query. I recommend avoiding using CSV in this manner. If you bring your data into SQL Server, you should normalize it.
– Tim Biegeleisen
Nov 13 '18 at 11:11
This is going to be one ugly query. I recommend avoiding using CSV in this manner. If you bring your data into SQL Server, you should normalize it.
– Tim Biegeleisen
Nov 13 '18 at 11:11
Which sql server version are you using?
– Salman A
Nov 13 '18 at 11:30
Which sql server version are you using?
– Salman A
Nov 13 '18 at 11:30
All '1' whether at the start,middle or end
– Deep patel
Nov 13 '18 at 11:53
All '1' whether at the start,middle or end
– Deep patel
Nov 13 '18 at 11:53
|
show 3 more comments
3 Answers
3
active
oldest
votes
1
I think the simplest method is:
SELECT TRIM(',' from REPLACE(',' + @ObjectValue + ',', ',1,', ','))
TRIM() is not available in older versions of SQL Server. One method is to replace the commas with spaces and using the available trim functions:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','), ',', ' '))), ' ', ','),
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
Need to replace,1,rather than just,1? (And replace with,?)
– MatBailie
Nov 13 '18 at 11:41
Fails for1,100,1(leaves00)
– Salman A
Nov 13 '18 at 11:50
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is',1,'.
– Gordon Linoff
Nov 13 '18 at 12:48
add a comment |
1
You need to double the delimiters in the list so that 1,1,1 becomes ,1,,1,,1,. Then replace all ,1, and cleanup afterwards:
SELECT ObjectValue, REPLACE(
LTRIM(RTRIM(
REPLACE(' ' + REPLACE(ObjectValue, ',', ' ') + ' ', ' 1 ', '')
)), ' ', ','
)
FROM (VALUES
('1'),
('1,1'),
('1,1,1'),
('0,1,0'),
('0,1,1,0'),
('0,1,1,1,0'),
('0,1,0,1,0'),
('1,0,0,1,0,0,1,1,0,1,0,1,1,1,1,1,0,1,0,0,1,1,0,1,0,1,0,1,0,1')
) AS v(ObjectValue)
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
That's also fine
– Deep patel
Nov 14 '18 at 4:36
add a comment |
0
-- SSMS 2017
DECLARE @ObjectValue VARCHAR(100) = '1,121,4,5,1,111,131,1'
SELECT TRIM(',' FROM REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','),',1,', ','))
-- SSMS 2016 or earlier
DECLARE @Replacetxt VARCHAR(MAX)
SET @ObjectValue=REPLACE(REPLACE(','+@ObjectValue+',', ',1,', ','), ',1,', ',') --Replace ',1,' with ','
SET @Replacetxt=REVERSE(SUBSTRING(@ObjectValue, PATINDEX('%[^,]%', @ObjectValue), LEN(@ObjectValue))) -- Remove comma from starting and reverse string
SELECT REVERSE(SUBSTRING(@Replacetxt, PATINDEX('%[^,]%', @Replacetxt), LEN(@ObjectValue))) -- Remove comma from ending and reverse string
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
Fails for1,1,1,1.
– Salman A
Nov 14 '18 at 6:40
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
});
}
});
draft saved
draft discarded
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%2f53279633%2fremove-all-instances-of-specific-value-from-comma-separated-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I think the simplest method is:
SELECT TRIM(',' from REPLACE(',' + @ObjectValue + ',', ',1,', ','))
TRIM() is not available in older versions of SQL Server. One method is to replace the commas with spaces and using the available trim functions:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','), ',', ' '))), ' ', ','),
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
Need to replace,1,rather than just,1? (And replace with,?)
– MatBailie
Nov 13 '18 at 11:41
Fails for1,100,1(leaves00)
– Salman A
Nov 13 '18 at 11:50
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is',1,'.
– Gordon Linoff
Nov 13 '18 at 12:48
add a comment |
1
I think the simplest method is:
SELECT TRIM(',' from REPLACE(',' + @ObjectValue + ',', ',1,', ','))
TRIM() is not available in older versions of SQL Server. One method is to replace the commas with spaces and using the available trim functions:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','), ',', ' '))), ' ', ','),
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
Need to replace,1,rather than just,1? (And replace with,?)
– MatBailie
Nov 13 '18 at 11:41
Fails for1,100,1(leaves00)
– Salman A
Nov 13 '18 at 11:50
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is',1,'.
– Gordon Linoff
Nov 13 '18 at 12:48
add a comment |
1
1
1
I think the simplest method is:
SELECT TRIM(',' from REPLACE(',' + @ObjectValue + ',', ',1,', ','))
TRIM() is not available in older versions of SQL Server. One method is to replace the commas with spaces and using the available trim functions:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','), ',', ' '))), ' ', ','),
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
I think the simplest method is:
SELECT TRIM(',' from REPLACE(',' + @ObjectValue + ',', ',1,', ','))
TRIM() is not available in older versions of SQL Server. One method is to replace the commas with spaces and using the available trim functions:
SELECT REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','), ',', ' '))), ' ', ','),
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
edited Nov 13 '18 at 12:47
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
answered Nov 13 '18 at 11:31
Gordon LinoffGordon Linoff
768k35300402
768k35300402
Need to replace,1,rather than just,1? (And replace with,?)
– MatBailie
Nov 13 '18 at 11:41
Fails for1,100,1(leaves00)
– Salman A
Nov 13 '18 at 11:50
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is',1,'.
– Gordon Linoff
Nov 13 '18 at 12:48
add a comment |
Need to replace,1,rather than just,1? (And replace with,?)
– MatBailie
Nov 13 '18 at 11:41
Fails for1,100,1(leaves00)
– Salman A
Nov 13 '18 at 11:50
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is',1,'.
– Gordon Linoff
Nov 13 '18 at 12:48
Need to replace
,1, rather than just ,1? (And replace with , ?)– MatBailie
Nov 13 '18 at 11:41
Need to replace
,1, rather than just ,1? (And replace with , ?)– MatBailie
Nov 13 '18 at 11:41
Fails for
1,100,1 (leaves 00)– Salman A
Nov 13 '18 at 11:50
Fails for
1,100,1 (leaves 00)– Salman A
Nov 13 '18 at 11:50
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
This gives me result 21,4,51131 that wrong
– Deep patel
Nov 13 '18 at 11:51
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is
',1,'.– Gordon Linoff
Nov 13 '18 at 12:48
@Deeppatel . . . There was a typo. All those commas and quotes, it is hard to keep track of which goes where. The value to replace is
',1,'.– Gordon Linoff
Nov 13 '18 at 12:48
add a comment |
1
You need to double the delimiters in the list so that 1,1,1 becomes ,1,,1,,1,. Then replace all ,1, and cleanup afterwards:
SELECT ObjectValue, REPLACE(
LTRIM(RTRIM(
REPLACE(' ' + REPLACE(ObjectValue, ',', ' ') + ' ', ' 1 ', '')
)), ' ', ','
)
FROM (VALUES
('1'),
('1,1'),
('1,1,1'),
('0,1,0'),
('0,1,1,0'),
('0,1,1,1,0'),
('0,1,0,1,0'),
('1,0,0,1,0,0,1,1,0,1,0,1,1,1,1,1,0,1,0,0,1,1,0,1,0,1,0,1,0,1')
) AS v(ObjectValue)
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
That's also fine
– Deep patel
Nov 14 '18 at 4:36
add a comment |
1
You need to double the delimiters in the list so that 1,1,1 becomes ,1,,1,,1,. Then replace all ,1, and cleanup afterwards:
SELECT ObjectValue, REPLACE(
LTRIM(RTRIM(
REPLACE(' ' + REPLACE(ObjectValue, ',', ' ') + ' ', ' 1 ', '')
)), ' ', ','
)
FROM (VALUES
('1'),
('1,1'),
('1,1,1'),
('0,1,0'),
('0,1,1,0'),
('0,1,1,1,0'),
('0,1,0,1,0'),
('1,0,0,1,0,0,1,1,0,1,0,1,1,1,1,1,0,1,0,0,1,1,0,1,0,1,0,1,0,1')
) AS v(ObjectValue)
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
That's also fine
– Deep patel
Nov 14 '18 at 4:36
add a comment |
1
1
1
You need to double the delimiters in the list so that 1,1,1 becomes ,1,,1,,1,. Then replace all ,1, and cleanup afterwards:
SELECT ObjectValue, REPLACE(
LTRIM(RTRIM(
REPLACE(' ' + REPLACE(ObjectValue, ',', ' ') + ' ', ' 1 ', '')
)), ' ', ','
)
FROM (VALUES
('1'),
('1,1'),
('1,1,1'),
('0,1,0'),
('0,1,1,0'),
('0,1,1,1,0'),
('0,1,0,1,0'),
('1,0,0,1,0,0,1,1,0,1,0,1,1,1,1,1,0,1,0,0,1,1,0,1,0,1,0,1,0,1')
) AS v(ObjectValue)
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
You need to double the delimiters in the list so that 1,1,1 becomes ,1,,1,,1,. Then replace all ,1, and cleanup afterwards:
SELECT ObjectValue, REPLACE(
LTRIM(RTRIM(
REPLACE(' ' + REPLACE(ObjectValue, ',', ' ') + ' ', ' 1 ', '')
)), ' ', ','
)
FROM (VALUES
('1'),
('1,1'),
('1,1,1'),
('0,1,0'),
('0,1,1,0'),
('0,1,1,1,0'),
('0,1,0,1,0'),
('1,0,0,1,0,0,1,1,0,1,0,1,1,1,1,1,0,1,0,0,1,1,0,1,0,1,0,1,0,1')
) AS v(ObjectValue)
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
edited Nov 14 '18 at 6:38
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
answered Nov 13 '18 at 12:19
Salman ASalman A
178k66338428
178k66338428
That's also fine
– Deep patel
Nov 14 '18 at 4:36
add a comment |
That's also fine
– Deep patel
Nov 14 '18 at 4:36
That's also fine
– Deep patel
Nov 14 '18 at 4:36
That's also fine
– Deep patel
Nov 14 '18 at 4:36
add a comment |
0
-- SSMS 2017
DECLARE @ObjectValue VARCHAR(100) = '1,121,4,5,1,111,131,1'
SELECT TRIM(',' FROM REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','),',1,', ','))
-- SSMS 2016 or earlier
DECLARE @Replacetxt VARCHAR(MAX)
SET @ObjectValue=REPLACE(REPLACE(','+@ObjectValue+',', ',1,', ','), ',1,', ',') --Replace ',1,' with ','
SET @Replacetxt=REVERSE(SUBSTRING(@ObjectValue, PATINDEX('%[^,]%', @ObjectValue), LEN(@ObjectValue))) -- Remove comma from starting and reverse string
SELECT REVERSE(SUBSTRING(@Replacetxt, PATINDEX('%[^,]%', @Replacetxt), LEN(@ObjectValue))) -- Remove comma from ending and reverse string
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
Fails for1,1,1,1.
– Salman A
Nov 14 '18 at 6:40
add a comment |
0
-- SSMS 2017
DECLARE @ObjectValue VARCHAR(100) = '1,121,4,5,1,111,131,1'
SELECT TRIM(',' FROM REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','),',1,', ','))
-- SSMS 2016 or earlier
DECLARE @Replacetxt VARCHAR(MAX)
SET @ObjectValue=REPLACE(REPLACE(','+@ObjectValue+',', ',1,', ','), ',1,', ',') --Replace ',1,' with ','
SET @Replacetxt=REVERSE(SUBSTRING(@ObjectValue, PATINDEX('%[^,]%', @ObjectValue), LEN(@ObjectValue))) -- Remove comma from starting and reverse string
SELECT REVERSE(SUBSTRING(@Replacetxt, PATINDEX('%[^,]%', @Replacetxt), LEN(@ObjectValue))) -- Remove comma from ending and reverse string
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
Fails for1,1,1,1.
– Salman A
Nov 14 '18 at 6:40
add a comment |
0
0
0
-- SSMS 2017
DECLARE @ObjectValue VARCHAR(100) = '1,121,4,5,1,111,131,1'
SELECT TRIM(',' FROM REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','),',1,', ','))
-- SSMS 2016 or earlier
DECLARE @Replacetxt VARCHAR(MAX)
SET @ObjectValue=REPLACE(REPLACE(','+@ObjectValue+',', ',1,', ','), ',1,', ',') --Replace ',1,' with ','
SET @Replacetxt=REVERSE(SUBSTRING(@ObjectValue, PATINDEX('%[^,]%', @ObjectValue), LEN(@ObjectValue))) -- Remove comma from starting and reverse string
SELECT REVERSE(SUBSTRING(@Replacetxt, PATINDEX('%[^,]%', @Replacetxt), LEN(@ObjectValue))) -- Remove comma from ending and reverse string
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
-- SSMS 2017
DECLARE @ObjectValue VARCHAR(100) = '1,121,4,5,1,111,131,1'
SELECT TRIM(',' FROM REPLACE(REPLACE(',' + @ObjectValue + ',', ',1,', ','),',1,', ','))
-- SSMS 2016 or earlier
DECLARE @Replacetxt VARCHAR(MAX)
SET @ObjectValue=REPLACE(REPLACE(','+@ObjectValue+',', ',1,', ','), ',1,', ',') --Replace ',1,' with ','
SET @Replacetxt=REVERSE(SUBSTRING(@ObjectValue, PATINDEX('%[^,]%', @ObjectValue), LEN(@ObjectValue))) -- Remove comma from starting and reverse string
SELECT REVERSE(SUBSTRING(@Replacetxt, PATINDEX('%[^,]%', @Replacetxt), LEN(@ObjectValue))) -- Remove comma from ending and reverse string
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
answered Nov 13 '18 at 12:33
Deep patelDeep patel
1216
1216
Fails for1,1,1,1.
– Salman A
Nov 14 '18 at 6:40
add a comment |
Fails for1,1,1,1.
– Salman A
Nov 14 '18 at 6:40
Fails for
1,1,1,1.– Salman A
Nov 14 '18 at 6:40
Fails for
1,1,1,1.– Salman A
Nov 14 '18 at 6:40
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53279633%2fremove-all-instances-of-specific-value-from-comma-separated-string%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
1
what is the condition ? Always the first and last ? Or Always
1? You need to specify the rules and condition– Squirrel
Nov 13 '18 at 11:07
1
This is (one of the) reason(s) you should never store cvs data. Normalize your data, and you'll never have this problem.
– HoneyBadger
Nov 13 '18 at 11:09
This is going to be one ugly query. I recommend avoiding using CSV in this manner. If you bring your data into SQL Server, you should normalize it.
– Tim Biegeleisen
Nov 13 '18 at 11:11
Which sql server version are you using?
– Salman A
Nov 13 '18 at 11:30
All '1' whether at the start,middle or end
– Deep patel
Nov 13 '18 at 11:53