I have scenario where we want to replace multiple double quotes to single quotes between the data, but as the input data is separated with "comma" delimiter and all column data is enclosed with double quotes "" got an issue and the same explained below:

The sample data looks like this:

"int","","123","abd"""sf123","top"

So, the output would be:

"int","","123","abd"sf123","top"

tried below approach to get the resolution, but only first occurrence is working, not sure what is the issue??

sed -ie 's/,"",/,"NULL",/g;s/""/"/g;s/,"NULL",/,"",/g' inputfile.txt

1. replacing all ---> from ,"", to ,"NULL",
2. replacing all multiple occurrences of ---> from """ or "" or """" to " (single occurrence)
3. replacing 1 step changes back to original ---> from ,"NULL", to ,"",

But, only first occurrence is getting changed and remaining looks same as below:

If input is :

"int","","","123","abd"""sf123","top"

the output is coming as:

"int","","NULL","123","abd"sf123","top"

But, the output should be:

"int","","","123","abd"sf123","top"

CodePudding user response：

You may try this perl with a lookahead:

perl -pe 's/("") (?=")//g' file

"int","","123","abd"sf123","top"
"int","","","123","abd"sf123","top"
"123"abcs"

Where input is:

cat file

"int","","123","abd"""sf123","top"
"int","","","123","abd"""sf123","top"
"123"""""abcs"

Breakup:

• ("") : Match 1 pairs of double quotes
• (?="): If those pairs are followed by a single "

CodePudding user response：

Using sed

$ sed -E 's/(,"",)?" (",)?/\1"\2/g' input_file
"int","","123","abd"sf123","top"
"int","","NULL","123","abd"sf123","top"
"int","","","123","abd"sf123","top"