Find a pattern, use and substitution for another match

Question

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

By seeing your profile came to know you never select any answer as correct answer, I would like to suggest you to please give it sometime and try to select an answer as correct one out of all posts. Even for your previous threads too you could do so. — RavinderSingh13, Jan 18 '19 at 14:39

score 2 · Accepted Answer · answered Jan 18 '19 at 15:12

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

RavinderSingh13 · Answer 2 · 2019-01-18T14:56:55.880

1

Could you please try following.

echo "INSERT INTO data VALUES (11,1), (NULL,2), (NULL,3), (NULL,4);"  |  
awk '
match($0,/\([0-9]+/){
  value=substr($0,RSTART+1,RLENGTH-1)
}
{
  gsub("NULL",value)
}
1'

Output will be as follows.

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

As per OP's comment in case you want to perform this operation for an Input_file then do following.(simply pass Input_file name to awk script)

awk '
match($0,/\([0-9]+/){
  value=substr($0,RSTART+1,RLENGTH-1)
}
{
  gsub("NULL",value)
}
1' Input_file

edited Jan 18 '19 at 14:56

answered Jan 18 '19 at 14:37

RavinderSingh13

130,504
14
57
93

actually i'm gonna ask another think i would like to do this line by line from a file? – muscleman71 Jan 18 '19 at 14:55
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' done < test.sql – muscleman71 Jan 18 '19 at 14:57
@muscleman71, please check my additonal comments now in my answer and let me know then? – RavinderSingh13 Jan 18 '19 at 14:57

stack0114106 · Answer 3 · 2019-01-18T15:33:02.830

1

You can try Perl also

$ cat muscleman.txt
INSERT INTO data VALUES (11,1), (NULL,2), (NULL,3), (NULL,4);
INSERT INTO data VALUES (22,1), (NULL,2), (NULL,3), (NULL,4);
INSERT INTO data VALUES (33,1), (NULL,2), (NULL,3), (NULL,4);
$  perl -pe ' /(\d+)/ and $x=$1; s/NULL/$x/g ' muscleman.txt
INSERT INTO data VALUES (11,1), (11,2), (11,3), (11,4);
INSERT INTO data VALUES (22,1), (22,2), (22,3), (22,4);
INSERT INTO data VALUES (33,1), (33,2), (33,3), (33,4);
$

Thanks @Sundeep for another shorter answer!

$ perl -pe '($x)=/(\d+)/; s/NULL/$x/g' muscleman.txt
INSERT INTO data VALUES (11,1), (11,2), (11,3), (11,4);
INSERT INTO data VALUES (22,1), (22,2), (22,3), (22,4);
INSERT INTO data VALUES (33,1), (33,2), (33,3), (33,4);
$

edited Jan 18 '19 at 15:33

answered Jan 18 '19 at 15:18

stack0114106

8,534
3
13
38

if you are a fan of golfing: `perl -pe '($x)=/(\d+)/; s/NULL/$x/g'` – Sundeep Jan 18 '19 at 15:29
@Sundeep glad that you liked it..I'll add yours to the answer.. btw I tried ````perl -pe ' /(\d+)/ && s/NULL/$1/g '```` why it didnt work?. – stack0114106 Jan 18 '19 at 15:31
I think that is because the substitute command will change `$&`, `$1`, etc – Sundeep Jan 18 '19 at 15:34
OK.. but it didn't introduce any capture groups in s/// - so it shouldn't change right? – stack0114106 Jan 18 '19 at 15:36
when you get time pls try golfing qstn - 54242508 – stack0114106 Jan 18 '19 at 15:37
every regex will have at least the default `$&` set.. and as far as I know, `$1`, `$2` will always change on any use of regex – Sundeep Jan 18 '19 at 15:38
ok.. got you.. thank you.. and pls review 54246409 and provide suggestions for any improvement.. – stack0114106 Jan 18 '19 at 15:42

Find a pattern, use and substitution for another match

3 Answers3