[SOLVED] Find a pattern, use and substitution for another match

Issue

I have the following string. I would like to find the first occurrence of digits in a line, use this match to replace the NULL's.

INSERT INTO data VALUES (11,1), (NULL,2), (NULL,3), (NULL,4);

I'm really not familiar with sed or awk at all.

so in the example above this is the output i want.

INSERT INTO data VALUES (11,1), (11,2), (11,3), (11,4);

this is kinda pseudo of the issue i think awk '{ sub(/{digit}/, "NULL", capturegroup }'

while read -r line; do
    name="$line"
    echo $line  |  
    awk '
    match($0,/\([0-9]+/){
      value=substr($0,RSTART+1,RLENGTH-1)
    }
    {
      gsub("NULL",value)
    }
    1' >> converted.sql
done < test.sql

Solution

With sed (tested on GNU sed 4.2.2, syntax will vary for different implementations)

$ cat ip.txt
INSERT INTO data VALUES (11,1), (NULL,2), (NULL,3), (NULL,4);
foo (42,14), (4,NULL), (5,3), (NULL,14)

$ sed -E ':a s/([0-9]+)(.*)NULL/\1\2\1/; ta' ip.txt
INSERT INTO data VALUES (11,1), (11,2), (11,3), (11,4);
foo (42,14), (4,42), (5,3), (42,14)

• -E to use ERE instead of default BRE
• :a label a
• ([0-9]+)(.*)NULL first sequence of digits, followed by other text, followed by last occurrence of NULL in the line
• \1\2\1 as per expected output
• ta branch to label a if the substitution succeeded
• See sed in-place editing if you need to change the input file itself

Answered By - Sundeep