CODEWITHSUNDEEP
sqlsql-servert-sqlreplacesql-function
ChawleeJay
ChawleeJay

Reputation: 57

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

Upvotes: 0

Views: 479

Answers (3)

OO7
OO7

Reputation: 690

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;

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

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 :)

Upvotes: 3

gwt
gwt

Reputation: 2413

You can try the following:

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

Upvotes: 1

Related Questions