Replace function in SQL Server

Question

I have a string of data

'["Dog",,,1,"Person","2020-03-17",,4,"Todd]'

I am trying to use the replace function to replace double commas with NULL values

Solution

'["Dog",NULL,NULL,1,"Person","2020-03-17",NULL,4,"Todd]'

But I keep ending up with

'"Dog",NULL,,1,"Person","2020-03-17",NULL,4,"Todd'

(The ,,, needs to become ,NULL,NULL, but only becomes ,NULL,,)

Here is my sample code I'm using

   REPLACE(FileData, ',,' , ',NULL,')
   WHERE FileData LIKE '%,,%'

Presumably, that code is part of an update or select, so it actually does something. — Gordon Linoff
– Gordon Linoff, Commented Mar 23, 2020 at 17:15
@Larnu the issue im facing is when there is three commas of missing data in a row. ',,,' I end up with ,NULL,, — ChawleeJay
– ChawleeJay, Commented Mar 23, 2020 at 17:21

MatBailie · Accepted Answer · 2020-03-23 17:58:00Z

3

If you do the same replacement twice, any number of sequential commas will get handled.

REPLACE(REPLACE(FileData, ',,' , ',NULL,'), ',,' , ',NULL,')

The first REPLACE deals with all the odd positions...

',,,,,,,,'` => ',NULL,,NULL,,NULL,,NULL,'

Doing it again will deal with all of the remaining positions.

=> ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'

Note, by specifically handling a special case of three consecutive commas (as in an other answer here) you won't handle four or five or six, etc. The above solution generalises to Any length of consecutive commas.

To be fully robust, you may also need to consider when there is a missing NULL at the first or last place in the string.

[,ThatOneToMyLeft,and,ThatOneToMyRight,]

A laborious but robust approach could be to replace [, and ,] with [,, and ,,] respectively, then do the double-replacement, then undo the first steps...

REPLACE(
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            FileData,
            '[,',
            '[,,'
          ),
          ',]',
          ',,]'
        ),
        ',,',
        ',NULL,'
      ),
      ',,',
      ',NULL,'
    ),
    ',]',
    ']',
  ),
  '[,',
  '['
)

There are ways to make even that less verbose, but I have to run right now :)

edited Mar 23, 2020 at 17:58

answered Mar 23, 2020 at 17:41

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zhorov Over a year ago

DECLARE @text varchar(max) = '["Dog",,]' is probably an edge case, but it's good approach. +1

MatBailie Over a year ago

@Zhorov - I'm actually pretty bummed that I didn't deal with that. It's been a long time since I had to deal with comma delimited strings in SQL, but even without the encasing square brackets the solution would be the same...

ChawleeJay Over a year ago

This is awesome. What was your approach of understanding the odd positions for the first replace?? @MatBailie

gwt · Accepted Answer · 2020-03-23 17:35:49Z

1

You can try the following:

REPLACE(REPLACE(FileData, ',,,' , ',NULL,,'), ',,' , ',NULL,')
   Where FileData LIKE '%,,%'

edited Mar 23, 2020 at 17:35

answered Mar 23, 2020 at 17:31

gwt

2,4334 gold badges40 silver badges61 bronze badges

2 Comments

MatBailie Over a year ago

The inner replacement need only be the same as the outer replacement. After all, the inner will then make ,,, into ,NULL,, and then the outer will finish the job. Nesting it as such would also mean that any length of ,,,,,,, will get handled. (',,,,,' => ,NULL,,NULL,,' => ',NULL,NULL,NULL,NULL,')

MatBailie Over a year ago

(Both replacements should be on ,, for maximum benefit. You don't need to handle special cases of three or more sequential commas. Doing it once as the OP has it will deal with all the odd numbered occurrences, 1st, 3rd, etc. Doing it again, in exactly the same way, will deal with All of the remaining occurrences.)

OO7 · Accepted Answer · 2020-03-24 02:44:10Z

You can create a function for your problem solving that associates to string replacement function.

Check this:

update table1
set column1 = dbo.ReplaceEx(column1, ',', 'NULL')
where column1 like '%,,%'

create function dbo.ReplaceEx(@string varchar(2000), @separator varchar(4), @nullValue varchar(10))
returns varchar(4000)
with execute as caller
as
begin
    declare @result varchar(4000);
    set @result = '';

    select @result = concat_ws(@sep, @result,
        case when rtrim(value) = '' then @nullValue
        else case when ltrim(rtrim(value)) = '[' then '[' + @nullValue
        else case when ltrim(rtrim(value)) = ']' then @nullValue + ']'
        else value end end end
     )
    from string_split(@string, @separator);

    return (@result);
end;

Collectives™ on Stack Overflow

Replace function in SQL Server

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related