SQL Server - remove character unless preceding or followed by specific character












0














Lets say I have a dataset like this:



Data
I have 23, chickens, but no cats
I have 23, chickensx, but no cats
I have 23, chickens,x but no cats


I want to remove every comma. Unless it is followed or preceding an x.
So in this case it should become:



Data
I have 23 chickens but no cats
I have 23 chickensx, but no cats
I have 23 chickens,x but no cats


Any ideas/suggestions on how to do this? I can have multiple comma's in one record and multiple that are followed or preceding an x.










share|improve this question






















  • Use a CLR function that allows you to do a regex replacement. Alternatively, scrub your data in an ETL package. T-SQL is a bad language to do string manipulation in.
    – Jeroen Mostert
    Nov 23 '18 at 20:29
















0














Lets say I have a dataset like this:



Data
I have 23, chickens, but no cats
I have 23, chickensx, but no cats
I have 23, chickens,x but no cats


I want to remove every comma. Unless it is followed or preceding an x.
So in this case it should become:



Data
I have 23 chickens but no cats
I have 23 chickensx, but no cats
I have 23 chickens,x but no cats


Any ideas/suggestions on how to do this? I can have multiple comma's in one record and multiple that are followed or preceding an x.










share|improve this question






















  • Use a CLR function that allows you to do a regex replacement. Alternatively, scrub your data in an ETL package. T-SQL is a bad language to do string manipulation in.
    – Jeroen Mostert
    Nov 23 '18 at 20:29














0












0








0







Lets say I have a dataset like this:



Data
I have 23, chickens, but no cats
I have 23, chickensx, but no cats
I have 23, chickens,x but no cats


I want to remove every comma. Unless it is followed or preceding an x.
So in this case it should become:



Data
I have 23 chickens but no cats
I have 23 chickensx, but no cats
I have 23 chickens,x but no cats


Any ideas/suggestions on how to do this? I can have multiple comma's in one record and multiple that are followed or preceding an x.










share|improve this question













Lets say I have a dataset like this:



Data
I have 23, chickens, but no cats
I have 23, chickensx, but no cats
I have 23, chickens,x but no cats


I want to remove every comma. Unless it is followed or preceding an x.
So in this case it should become:



Data
I have 23 chickens but no cats
I have 23 chickensx, but no cats
I have 23 chickens,x but no cats


Any ideas/suggestions on how to do this? I can have multiple comma's in one record and multiple that are followed or preceding an x.







sql-server tsql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 23 '18 at 12:34









DenStudentDenStudent

5521625




5521625












  • Use a CLR function that allows you to do a regex replacement. Alternatively, scrub your data in an ETL package. T-SQL is a bad language to do string manipulation in.
    – Jeroen Mostert
    Nov 23 '18 at 20:29


















  • Use a CLR function that allows you to do a regex replacement. Alternatively, scrub your data in an ETL package. T-SQL is a bad language to do string manipulation in.
    – Jeroen Mostert
    Nov 23 '18 at 20:29
















Use a CLR function that allows you to do a regex replacement. Alternatively, scrub your data in an ETL package. T-SQL is a bad language to do string manipulation in.
– Jeroen Mostert
Nov 23 '18 at 20:29




Use a CLR function that allows you to do a regex replacement. Alternatively, scrub your data in an ETL package. T-SQL is a bad language to do string manipulation in.
– Jeroen Mostert
Nov 23 '18 at 20:29












4 Answers
4






active

oldest

votes


















2














I don't think this kind of work should be in the database but



CREATE TABLE T (DATA VARCHAR(100));

INSERT INTO T(DATA) VALUES
('I have 23 , chickens, ,x xw, but no cats'),
('I have 23 , chickens, but no ,@, cats'),
('I have 23, chickens, but no cats'),
('I have 23, chickensx, but no cats'),
(', 23!, I have 23, chickens,x, but no cats'),
(' , I have 23, chickens,x but no cats ,x _, _'),
('x,abc , !, x,x,');

DECLARE @DATA VARCHAR(MAX) = '';

SELECT @DATA = STRING_AGG(DATA, CHAR(9))
FROM T;

WHILE (SELECT PATINDEX('%[^x,%],[^x,%]%', @DATA)) > 0
BEGIN
SET @DATA = STUFF(@DATA, PATINDEX('%[^x,%],[^x,%]%', @DATA) + 1, 1, '');
END

SELECT *
FROM STRING_SPLIT(@DATA, CHAR(9));


Returns:



+-------------------------------------------+
| value |
+-------------------------------------------+
| I have 23 chickens ,x xw but no cats |
| I have 23 chickens but no @ cats |
| I have 23 chickens but no cats |
| I have 23 chickensx, but no cats |
| 23! I have 23 chickens,x, but no cats |
| I have 23 chickens,x but no cats ,x _ _ |
| x,abc ! x,x, |
+-------------------------------------------+


Demo



Or by calling REPLACE() function 7 times, with CHAR() as



SELECT Data,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(Data, ',x,', CHAR(1)),
',x', CHAR(2)
),
'x,', CHAR(9)),
',', ''),
CHAR(1), ',x,'),
CHAR(2), ',x'),
CHAR(9), 'x,') Results
FROM T;


Order is from inside to outside (from the inner replace to the top replace)




  • Find ',x,' and replace it with CHAR(1).

  • Find ',x' and relace it with CHAR(2).

  • Find 'x,' and replace it with CHAR(9).

  • Find all ',' and replace them with ''.

  • Get back ',x,'.

  • Get back ',x'.

  • Get back 'x,'.


Demo






share|improve this answer



















  • 1




    Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
    – Jeroen Mostert
    Nov 23 '18 at 20:25












  • @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
    – Sami
    Nov 23 '18 at 20:29










  • How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
    – Jeroen Mostert
    Nov 23 '18 at 20:34










  • @JeroenMostert and now?
    – Sami
    Nov 23 '18 at 22:13










  • Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
    – Jeroen Mostert
    Nov 26 '18 at 10:16



















2














You could use multiple REPLACE:



SELECT col,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
col,',x,','#'),',x','~'),'x,','^'),',',''),'~',',x'),'^','x,'),'#',',x,')
FROM tab


db<>fiddle demo






share|improve this answer



















  • 1




    Fails for ,x,
    – Salman A
    Nov 23 '18 at 13:35










  • I won't use thosechars since they could be in the data.
    – Sami
    Nov 24 '18 at 1:59



















1














It's not particularly pretty, but you could use REPLACE to change the character(s) of 'x,' and 'x,' to something else, replace all the commas and then change the other characters back:



WITH VTE AS(
SELECT String
FROM (VALUES ('I have 23, chickens, but no cats'),
('I have 23, chickensx, but no cats'),
('I have 23, chickens,x but no cats'))V(String))
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'x,',CHAR(1)),',x',CHAR(2)),',',''),CHAR(2),',x'),CHAR(1),'x,') AS NewString
FROM VTE;


An important thing to note is to choose a character that doesn't appear in your string (hence my choice of CHAR(1) and CHAR(2), as they aren't "typeable" characters).






share|improve this answer



















  • 1




    Fails for chickens,x, and ,x,
    – Sami
    Nov 24 '18 at 1:55



















1














It depends.



As a one-off task – 'I have a load of data I need to manipulate' – you can do it by a sequence of




  • put-in-escape-codes

  • remove commas

  • unescape escape codes.


You can eyeball your data to make sure you choose escape codes not in the data set. e.g.:



CREATE TABLE T (DATA VARCHAR(100));

INSERT INTO T(DATA) VALUES
('I have 23 , chickens, ,x xw, but no cats'),
('I have 23 , chickens, but no ,@, cats'),
('I have 23, chickens, but no cats'),
('I have 23, chickensx, but no cats'),
(', 23!, I have 23, chickens,x, but no cats'),
(' , I have 23, chickens,x but no cats ,x _, _'),
('x,abc , !, x,x,'),
('I have 23 , chickens, but no cats'),
('I have 23!, chickens, but no cats'),
('chickens,x, and ,x,'),
(',x,x,')


Select
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(Data,
',x,', '______'),
',x' , '___[___'),
'x,' , '___]___'),

',' , ''),

'___]___' , 'x,'),
'___[___' , ',x'),
'______' , ',x,')

from T


But to do this as a reliable repeatable task on data you don't personally inspect, then @Jeroen-Mostert's point about the CLR or ETL is the better way to go.



You could still do it with crude Replace(Replace(.... I think. It would have to be




  • Choose some escape characters

  • Escape each escape characters (e.g. by doubling each _ [ ]

  • Escape each sequence that matters ,x, x, ,x

  • Remove commas

  • Unescape sequences

  • Unescape escape characters.


I think that would be a total of 13 nested Replaces.






share|improve this answer























    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


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446816%2fsql-server-remove-character-unless-preceding-or-followed-by-specific-character%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    4 Answers
    4






    active

    oldest

    votes








    4 Answers
    4






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    I don't think this kind of work should be in the database but



    CREATE TABLE T (DATA VARCHAR(100));

    INSERT INTO T(DATA) VALUES
    ('I have 23 , chickens, ,x xw, but no cats'),
    ('I have 23 , chickens, but no ,@, cats'),
    ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    (', 23!, I have 23, chickens,x, but no cats'),
    (' , I have 23, chickens,x but no cats ,x _, _'),
    ('x,abc , !, x,x,');

    DECLARE @DATA VARCHAR(MAX) = '';

    SELECT @DATA = STRING_AGG(DATA, CHAR(9))
    FROM T;

    WHILE (SELECT PATINDEX('%[^x,%],[^x,%]%', @DATA)) > 0
    BEGIN
    SET @DATA = STUFF(@DATA, PATINDEX('%[^x,%],[^x,%]%', @DATA) + 1, 1, '');
    END

    SELECT *
    FROM STRING_SPLIT(@DATA, CHAR(9));


    Returns:



    +-------------------------------------------+
    | value |
    +-------------------------------------------+
    | I have 23 chickens ,x xw but no cats |
    | I have 23 chickens but no @ cats |
    | I have 23 chickens but no cats |
    | I have 23 chickensx, but no cats |
    | 23! I have 23 chickens,x, but no cats |
    | I have 23 chickens,x but no cats ,x _ _ |
    | x,abc ! x,x, |
    +-------------------------------------------+


    Demo



    Or by calling REPLACE() function 7 times, with CHAR() as



    SELECT Data,
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(Data, ',x,', CHAR(1)),
    ',x', CHAR(2)
    ),
    'x,', CHAR(9)),
    ',', ''),
    CHAR(1), ',x,'),
    CHAR(2), ',x'),
    CHAR(9), 'x,') Results
    FROM T;


    Order is from inside to outside (from the inner replace to the top replace)




    • Find ',x,' and replace it with CHAR(1).

    • Find ',x' and relace it with CHAR(2).

    • Find 'x,' and replace it with CHAR(9).

    • Find all ',' and replace them with ''.

    • Get back ',x,'.

    • Get back ',x'.

    • Get back 'x,'.


    Demo






    share|improve this answer



















    • 1




      Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
      – Jeroen Mostert
      Nov 23 '18 at 20:25












    • @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
      – Sami
      Nov 23 '18 at 20:29










    • How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
      – Jeroen Mostert
      Nov 23 '18 at 20:34










    • @JeroenMostert and now?
      – Sami
      Nov 23 '18 at 22:13










    • Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
      – Jeroen Mostert
      Nov 26 '18 at 10:16
















    2














    I don't think this kind of work should be in the database but



    CREATE TABLE T (DATA VARCHAR(100));

    INSERT INTO T(DATA) VALUES
    ('I have 23 , chickens, ,x xw, but no cats'),
    ('I have 23 , chickens, but no ,@, cats'),
    ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    (', 23!, I have 23, chickens,x, but no cats'),
    (' , I have 23, chickens,x but no cats ,x _, _'),
    ('x,abc , !, x,x,');

    DECLARE @DATA VARCHAR(MAX) = '';

    SELECT @DATA = STRING_AGG(DATA, CHAR(9))
    FROM T;

    WHILE (SELECT PATINDEX('%[^x,%],[^x,%]%', @DATA)) > 0
    BEGIN
    SET @DATA = STUFF(@DATA, PATINDEX('%[^x,%],[^x,%]%', @DATA) + 1, 1, '');
    END

    SELECT *
    FROM STRING_SPLIT(@DATA, CHAR(9));


    Returns:



    +-------------------------------------------+
    | value |
    +-------------------------------------------+
    | I have 23 chickens ,x xw but no cats |
    | I have 23 chickens but no @ cats |
    | I have 23 chickens but no cats |
    | I have 23 chickensx, but no cats |
    | 23! I have 23 chickens,x, but no cats |
    | I have 23 chickens,x but no cats ,x _ _ |
    | x,abc ! x,x, |
    +-------------------------------------------+


    Demo



    Or by calling REPLACE() function 7 times, with CHAR() as



    SELECT Data,
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(Data, ',x,', CHAR(1)),
    ',x', CHAR(2)
    ),
    'x,', CHAR(9)),
    ',', ''),
    CHAR(1), ',x,'),
    CHAR(2), ',x'),
    CHAR(9), 'x,') Results
    FROM T;


    Order is from inside to outside (from the inner replace to the top replace)




    • Find ',x,' and replace it with CHAR(1).

    • Find ',x' and relace it with CHAR(2).

    • Find 'x,' and replace it with CHAR(9).

    • Find all ',' and replace them with ''.

    • Get back ',x,'.

    • Get back ',x'.

    • Get back 'x,'.


    Demo






    share|improve this answer



















    • 1




      Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
      – Jeroen Mostert
      Nov 23 '18 at 20:25












    • @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
      – Sami
      Nov 23 '18 at 20:29










    • How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
      – Jeroen Mostert
      Nov 23 '18 at 20:34










    • @JeroenMostert and now?
      – Sami
      Nov 23 '18 at 22:13










    • Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
      – Jeroen Mostert
      Nov 26 '18 at 10:16














    2












    2








    2






    I don't think this kind of work should be in the database but



    CREATE TABLE T (DATA VARCHAR(100));

    INSERT INTO T(DATA) VALUES
    ('I have 23 , chickens, ,x xw, but no cats'),
    ('I have 23 , chickens, but no ,@, cats'),
    ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    (', 23!, I have 23, chickens,x, but no cats'),
    (' , I have 23, chickens,x but no cats ,x _, _'),
    ('x,abc , !, x,x,');

    DECLARE @DATA VARCHAR(MAX) = '';

    SELECT @DATA = STRING_AGG(DATA, CHAR(9))
    FROM T;

    WHILE (SELECT PATINDEX('%[^x,%],[^x,%]%', @DATA)) > 0
    BEGIN
    SET @DATA = STUFF(@DATA, PATINDEX('%[^x,%],[^x,%]%', @DATA) + 1, 1, '');
    END

    SELECT *
    FROM STRING_SPLIT(@DATA, CHAR(9));


    Returns:



    +-------------------------------------------+
    | value |
    +-------------------------------------------+
    | I have 23 chickens ,x xw but no cats |
    | I have 23 chickens but no @ cats |
    | I have 23 chickens but no cats |
    | I have 23 chickensx, but no cats |
    | 23! I have 23 chickens,x, but no cats |
    | I have 23 chickens,x but no cats ,x _ _ |
    | x,abc ! x,x, |
    +-------------------------------------------+


    Demo



    Or by calling REPLACE() function 7 times, with CHAR() as



    SELECT Data,
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(Data, ',x,', CHAR(1)),
    ',x', CHAR(2)
    ),
    'x,', CHAR(9)),
    ',', ''),
    CHAR(1), ',x,'),
    CHAR(2), ',x'),
    CHAR(9), 'x,') Results
    FROM T;


    Order is from inside to outside (from the inner replace to the top replace)




    • Find ',x,' and replace it with CHAR(1).

    • Find ',x' and relace it with CHAR(2).

    • Find 'x,' and replace it with CHAR(9).

    • Find all ',' and replace them with ''.

    • Get back ',x,'.

    • Get back ',x'.

    • Get back 'x,'.


    Demo






    share|improve this answer














    I don't think this kind of work should be in the database but



    CREATE TABLE T (DATA VARCHAR(100));

    INSERT INTO T(DATA) VALUES
    ('I have 23 , chickens, ,x xw, but no cats'),
    ('I have 23 , chickens, but no ,@, cats'),
    ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    (', 23!, I have 23, chickens,x, but no cats'),
    (' , I have 23, chickens,x but no cats ,x _, _'),
    ('x,abc , !, x,x,');

    DECLARE @DATA VARCHAR(MAX) = '';

    SELECT @DATA = STRING_AGG(DATA, CHAR(9))
    FROM T;

    WHILE (SELECT PATINDEX('%[^x,%],[^x,%]%', @DATA)) > 0
    BEGIN
    SET @DATA = STUFF(@DATA, PATINDEX('%[^x,%],[^x,%]%', @DATA) + 1, 1, '');
    END

    SELECT *
    FROM STRING_SPLIT(@DATA, CHAR(9));


    Returns:



    +-------------------------------------------+
    | value |
    +-------------------------------------------+
    | I have 23 chickens ,x xw but no cats |
    | I have 23 chickens but no @ cats |
    | I have 23 chickens but no cats |
    | I have 23 chickensx, but no cats |
    | 23! I have 23 chickens,x, but no cats |
    | I have 23 chickens,x but no cats ,x _ _ |
    | x,abc ! x,x, |
    +-------------------------------------------+


    Demo



    Or by calling REPLACE() function 7 times, with CHAR() as



    SELECT Data,
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(Data, ',x,', CHAR(1)),
    ',x', CHAR(2)
    ),
    'x,', CHAR(9)),
    ',', ''),
    CHAR(1), ',x,'),
    CHAR(2), ',x'),
    CHAR(9), 'x,') Results
    FROM T;


    Order is from inside to outside (from the inner replace to the top replace)




    • Find ',x,' and replace it with CHAR(1).

    • Find ',x' and relace it with CHAR(2).

    • Find 'x,' and replace it with CHAR(9).

    • Find all ',' and replace them with ''.

    • Get back ',x,'.

    • Get back ',x'.

    • Get back 'x,'.


    Demo







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 24 '18 at 2:16

























    answered Nov 23 '18 at 19:51









    SamiSami

    7,81931040




    7,81931040








    • 1




      Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
      – Jeroen Mostert
      Nov 23 '18 at 20:25












    • @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
      – Sami
      Nov 23 '18 at 20:29










    • How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
      – Jeroen Mostert
      Nov 23 '18 at 20:34










    • @JeroenMostert and now?
      – Sami
      Nov 23 '18 at 22:13










    • Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
      – Jeroen Mostert
      Nov 26 '18 at 10:16














    • 1




      Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
      – Jeroen Mostert
      Nov 23 '18 at 20:25












    • @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
      – Sami
      Nov 23 '18 at 20:29










    • How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
      – Jeroen Mostert
      Nov 23 '18 at 20:34










    • @JeroenMostert and now?
      – Sami
      Nov 23 '18 at 22:13










    • Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
      – Jeroen Mostert
      Nov 26 '18 at 10:16








    1




    1




    Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
    – Jeroen Mostert
    Nov 23 '18 at 20:25






    Fails spectacularly on 'I have 23 , chickens, but no cats'. Yes, that wasn't one of the sample inputs, but this still seems far too brittle.
    – Jeroen Mostert
    Nov 23 '18 at 20:25














    @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
    – Sami
    Nov 23 '18 at 20:29




    @JeroenMostert Thanks for the comment STUFF(DATA, PATINDEX('%[0-9] ,%', DATA) +1, 1, '')
    – Sami
    Nov 23 '18 at 20:29












    How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
    – Jeroen Mostert
    Nov 23 '18 at 20:34




    How's that any improvement? That fails on 'I have 23!, chickens, but no cats'. For that matter, it fails on 'action 1, 2, 3'. The pertinent part of the question is "every comma, unless followed by or preceded by an x" -- the presence or absence of numbers, spaces or exclamation marks isn't relevant.
    – Jeroen Mostert
    Nov 23 '18 at 20:34












    @JeroenMostert and now?
    – Sami
    Nov 23 '18 at 22:13




    @JeroenMostert and now?
    – Sami
    Nov 23 '18 at 22:13












    Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
    – Jeroen Mostert
    Nov 26 '18 at 10:16




    Now it's a really good defense of your first sentence (showing why this shouldn't be done in T-SQL). :-)
    – Jeroen Mostert
    Nov 26 '18 at 10:16













    2














    You could use multiple REPLACE:



    SELECT col,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    col,',x,','#'),',x','~'),'x,','^'),',',''),'~',',x'),'^','x,'),'#',',x,')
    FROM tab


    db<>fiddle demo






    share|improve this answer



















    • 1




      Fails for ,x,
      – Salman A
      Nov 23 '18 at 13:35










    • I won't use thosechars since they could be in the data.
      – Sami
      Nov 24 '18 at 1:59
















    2














    You could use multiple REPLACE:



    SELECT col,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    col,',x,','#'),',x','~'),'x,','^'),',',''),'~',',x'),'^','x,'),'#',',x,')
    FROM tab


    db<>fiddle demo






    share|improve this answer



















    • 1




      Fails for ,x,
      – Salman A
      Nov 23 '18 at 13:35










    • I won't use thosechars since they could be in the data.
      – Sami
      Nov 24 '18 at 1:59














    2












    2








    2






    You could use multiple REPLACE:



    SELECT col,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    col,',x,','#'),',x','~'),'x,','^'),',',''),'~',',x'),'^','x,'),'#',',x,')
    FROM tab


    db<>fiddle demo






    share|improve this answer














    You could use multiple REPLACE:



    SELECT col,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    col,',x,','#'),',x','~'),'x,','^'),',',''),'~',',x'),'^','x,'),'#',',x,')
    FROM tab


    db<>fiddle demo







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 23 '18 at 15:47

























    answered Nov 23 '18 at 12:37









    Lukasz SzozdaLukasz Szozda

    78.8k1061105




    78.8k1061105








    • 1




      Fails for ,x,
      – Salman A
      Nov 23 '18 at 13:35










    • I won't use thosechars since they could be in the data.
      – Sami
      Nov 24 '18 at 1:59














    • 1




      Fails for ,x,
      – Salman A
      Nov 23 '18 at 13:35










    • I won't use thosechars since they could be in the data.
      – Sami
      Nov 24 '18 at 1:59








    1




    1




    Fails for ,x,
    – Salman A
    Nov 23 '18 at 13:35




    Fails for ,x,
    – Salman A
    Nov 23 '18 at 13:35












    I won't use thosechars since they could be in the data.
    – Sami
    Nov 24 '18 at 1:59




    I won't use thosechars since they could be in the data.
    – Sami
    Nov 24 '18 at 1:59











    1














    It's not particularly pretty, but you could use REPLACE to change the character(s) of 'x,' and 'x,' to something else, replace all the commas and then change the other characters back:



    WITH VTE AS(
    SELECT String
    FROM (VALUES ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    ('I have 23, chickens,x but no cats'))V(String))
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'x,',CHAR(1)),',x',CHAR(2)),',',''),CHAR(2),',x'),CHAR(1),'x,') AS NewString
    FROM VTE;


    An important thing to note is to choose a character that doesn't appear in your string (hence my choice of CHAR(1) and CHAR(2), as they aren't "typeable" characters).






    share|improve this answer



















    • 1




      Fails for chickens,x, and ,x,
      – Sami
      Nov 24 '18 at 1:55
















    1














    It's not particularly pretty, but you could use REPLACE to change the character(s) of 'x,' and 'x,' to something else, replace all the commas and then change the other characters back:



    WITH VTE AS(
    SELECT String
    FROM (VALUES ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    ('I have 23, chickens,x but no cats'))V(String))
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'x,',CHAR(1)),',x',CHAR(2)),',',''),CHAR(2),',x'),CHAR(1),'x,') AS NewString
    FROM VTE;


    An important thing to note is to choose a character that doesn't appear in your string (hence my choice of CHAR(1) and CHAR(2), as they aren't "typeable" characters).






    share|improve this answer



















    • 1




      Fails for chickens,x, and ,x,
      – Sami
      Nov 24 '18 at 1:55














    1












    1








    1






    It's not particularly pretty, but you could use REPLACE to change the character(s) of 'x,' and 'x,' to something else, replace all the commas and then change the other characters back:



    WITH VTE AS(
    SELECT String
    FROM (VALUES ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    ('I have 23, chickens,x but no cats'))V(String))
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'x,',CHAR(1)),',x',CHAR(2)),',',''),CHAR(2),',x'),CHAR(1),'x,') AS NewString
    FROM VTE;


    An important thing to note is to choose a character that doesn't appear in your string (hence my choice of CHAR(1) and CHAR(2), as they aren't "typeable" characters).






    share|improve this answer














    It's not particularly pretty, but you could use REPLACE to change the character(s) of 'x,' and 'x,' to something else, replace all the commas and then change the other characters back:



    WITH VTE AS(
    SELECT String
    FROM (VALUES ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    ('I have 23, chickens,x but no cats'))V(String))
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'x,',CHAR(1)),',x',CHAR(2)),',',''),CHAR(2),',x'),CHAR(1),'x,') AS NewString
    FROM VTE;


    An important thing to note is to choose a character that doesn't appear in your string (hence my choice of CHAR(1) and CHAR(2), as they aren't "typeable" characters).







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 23 '18 at 12:50

























    answered Nov 23 '18 at 12:39









    LarnuLarnu

    16.1k41630




    16.1k41630








    • 1




      Fails for chickens,x, and ,x,
      – Sami
      Nov 24 '18 at 1:55














    • 1




      Fails for chickens,x, and ,x,
      – Sami
      Nov 24 '18 at 1:55








    1




    1




    Fails for chickens,x, and ,x,
    – Sami
    Nov 24 '18 at 1:55




    Fails for chickens,x, and ,x,
    – Sami
    Nov 24 '18 at 1:55











    1














    It depends.



    As a one-off task – 'I have a load of data I need to manipulate' – you can do it by a sequence of




    • put-in-escape-codes

    • remove commas

    • unescape escape codes.


    You can eyeball your data to make sure you choose escape codes not in the data set. e.g.:



    CREATE TABLE T (DATA VARCHAR(100));

    INSERT INTO T(DATA) VALUES
    ('I have 23 , chickens, ,x xw, but no cats'),
    ('I have 23 , chickens, but no ,@, cats'),
    ('I have 23, chickens, but no cats'),
    ('I have 23, chickensx, but no cats'),
    (', 23!, I have 23, chickens,x, but no cats'),
    (' , I have 23, chickens,x but no cats ,x _, _'),
    ('x,abc , !, x,x,'),
    ('I have 23 , chickens, but no cats'),
    ('I have 23!, chickens, but no cats'),
    ('chickens,x, and ,x,'),
    (',x,x,')


    Select
    Replace(
    Replace(
    Replace(
    Replace(
    Replace(
    Replace(
    Replace(Data,
    ',x,', '______'),
    ',x' , '___[___'),
    'x,' , '___]___'),

    ',' , ''),

    '___]___' , 'x,'),
    '___[___' , ',x'),
    '______' , ',x,')

    from T


    But to do this as a reliable repeatable task on data you don't personally inspect, then @Jeroen-Mostert's point about the CLR or ETL is the better way to go.



    You could still do it with crude Replace(Replace(.... I think. It would have to be




    • Choose some escape characters

    • Escape each escape characters (e.g. by doubling each _ [ ]

    • Escape each sequence that matters ,x, x, ,x

    • Remove commas

    • Unescape sequences

    • Unescape escape characters.


    I think that would be a total of 13 nested Replaces.






    share|improve this answer




























      1














      It depends.



      As a one-off task – 'I have a load of data I need to manipulate' – you can do it by a sequence of




      • put-in-escape-codes

      • remove commas

      • unescape escape codes.


      You can eyeball your data to make sure you choose escape codes not in the data set. e.g.:



      CREATE TABLE T (DATA VARCHAR(100));

      INSERT INTO T(DATA) VALUES
      ('I have 23 , chickens, ,x xw, but no cats'),
      ('I have 23 , chickens, but no ,@, cats'),
      ('I have 23, chickens, but no cats'),
      ('I have 23, chickensx, but no cats'),
      (', 23!, I have 23, chickens,x, but no cats'),
      (' , I have 23, chickens,x but no cats ,x _, _'),
      ('x,abc , !, x,x,'),
      ('I have 23 , chickens, but no cats'),
      ('I have 23!, chickens, but no cats'),
      ('chickens,x, and ,x,'),
      (',x,x,')


      Select
      Replace(
      Replace(
      Replace(
      Replace(
      Replace(
      Replace(
      Replace(Data,
      ',x,', '______'),
      ',x' , '___[___'),
      'x,' , '___]___'),

      ',' , ''),

      '___]___' , 'x,'),
      '___[___' , ',x'),
      '______' , ',x,')

      from T


      But to do this as a reliable repeatable task on data you don't personally inspect, then @Jeroen-Mostert's point about the CLR or ETL is the better way to go.



      You could still do it with crude Replace(Replace(.... I think. It would have to be




      • Choose some escape characters

      • Escape each escape characters (e.g. by doubling each _ [ ]

      • Escape each sequence that matters ,x, x, ,x

      • Remove commas

      • Unescape sequences

      • Unescape escape characters.


      I think that would be a total of 13 nested Replaces.






      share|improve this answer


























        1












        1








        1






        It depends.



        As a one-off task – 'I have a load of data I need to manipulate' – you can do it by a sequence of




        • put-in-escape-codes

        • remove commas

        • unescape escape codes.


        You can eyeball your data to make sure you choose escape codes not in the data set. e.g.:



        CREATE TABLE T (DATA VARCHAR(100));

        INSERT INTO T(DATA) VALUES
        ('I have 23 , chickens, ,x xw, but no cats'),
        ('I have 23 , chickens, but no ,@, cats'),
        ('I have 23, chickens, but no cats'),
        ('I have 23, chickensx, but no cats'),
        (', 23!, I have 23, chickens,x, but no cats'),
        (' , I have 23, chickens,x but no cats ,x _, _'),
        ('x,abc , !, x,x,'),
        ('I have 23 , chickens, but no cats'),
        ('I have 23!, chickens, but no cats'),
        ('chickens,x, and ,x,'),
        (',x,x,')


        Select
        Replace(
        Replace(
        Replace(
        Replace(
        Replace(
        Replace(
        Replace(Data,
        ',x,', '______'),
        ',x' , '___[___'),
        'x,' , '___]___'),

        ',' , ''),

        '___]___' , 'x,'),
        '___[___' , ',x'),
        '______' , ',x,')

        from T


        But to do this as a reliable repeatable task on data you don't personally inspect, then @Jeroen-Mostert's point about the CLR or ETL is the better way to go.



        You could still do it with crude Replace(Replace(.... I think. It would have to be




        • Choose some escape characters

        • Escape each escape characters (e.g. by doubling each _ [ ]

        • Escape each sequence that matters ,x, x, ,x

        • Remove commas

        • Unescape sequences

        • Unescape escape characters.


        I think that would be a total of 13 nested Replaces.






        share|improve this answer














        It depends.



        As a one-off task – 'I have a load of data I need to manipulate' – you can do it by a sequence of




        • put-in-escape-codes

        • remove commas

        • unescape escape codes.


        You can eyeball your data to make sure you choose escape codes not in the data set. e.g.:



        CREATE TABLE T (DATA VARCHAR(100));

        INSERT INTO T(DATA) VALUES
        ('I have 23 , chickens, ,x xw, but no cats'),
        ('I have 23 , chickens, but no ,@, cats'),
        ('I have 23, chickens, but no cats'),
        ('I have 23, chickensx, but no cats'),
        (', 23!, I have 23, chickens,x, but no cats'),
        (' , I have 23, chickens,x but no cats ,x _, _'),
        ('x,abc , !, x,x,'),
        ('I have 23 , chickens, but no cats'),
        ('I have 23!, chickens, but no cats'),
        ('chickens,x, and ,x,'),
        (',x,x,')


        Select
        Replace(
        Replace(
        Replace(
        Replace(
        Replace(
        Replace(
        Replace(Data,
        ',x,', '______'),
        ',x' , '___[___'),
        'x,' , '___]___'),

        ',' , ''),

        '___]___' , 'x,'),
        '___[___' , ',x'),
        '______' , ',x,')

        from T


        But to do this as a reliable repeatable task on data you don't personally inspect, then @Jeroen-Mostert's point about the CLR or ETL is the better way to go.



        You could still do it with crude Replace(Replace(.... I think. It would have to be




        • Choose some escape characters

        • Escape each escape characters (e.g. by doubling each _ [ ]

        • Escape each sequence that matters ,x, x, ,x

        • Remove commas

        • Unescape sequences

        • Unescape escape characters.


        I think that would be a total of 13 nested Replaces.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 24 '18 at 18:20

























        answered Nov 24 '18 at 2:35









        Chris F CarrollChris F Carroll

        4,33813142




        4,33813142






























            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.





            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.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446816%2fsql-server-remove-character-unless-preceding-or-followed-by-specific-character%23new-answer', 'question_page');
            }
            );

            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







            Popular posts from this blog

            What visual should I use to simply compare current year value vs last year in Power BI desktop

            How to ignore python UserWarning in pytest?

            Alexandru Averescu