Replace function in SQL Server

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 '%,,%'

Answer

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 🙂