Sed remove NULL but only when the NULL means empty or no value

Question

Exporting a table from MySQL where fields that have no value will have the keyword NULL within.

| id | name | nickname | origin | date |
| 1  | Joe  | Mini-J   | BRAZIL | NULL |

I have written a script to automatically remove all occurrences of NULL using a one-liner sed, which will remove the NULL in date column correctly:

sed -i 's/NULL//g'

However, how do we handle IF we have the following?

| id | name | nickname | origin | date |
| 1  | Joe  | Mini-J   | BRAZIL | NULL |
| 2  | Dees | DJ Null Bee| US| 2017-04-01 |
| 3  | NULL AND VOID | NULLZIET | NULL| 2016-05-13 |
| 4  | Pablo | ALA PUHU MINULLE | GERMANY| 2017-02-14 |

Apparently, the global search and replace all occurrences of NULL will be removed, where even "ALA PUHU MINULLE" will become "ALA PUHU MIE", which is incorrect.

I suppose the use of regex perhaps can be useful to apply the rule? But if so, will "DJ Null Bee" be affected or will it become "DJ Bee"? The desired outcome should really be:

| id | name | nickname | origin | date |
| 1  | Joe  | Mini-J   | BRAZIL |  |
| 2  | Dees | DJ Null Bee| US| 2017-04-01 |
| 3  | NULL AND VOID | DJ Null Bee| | 2016-05-13 |
| 4  | Pablo | ALA PUHU MINULLE | GERMANY| 2017-02-14 |

Given that NULL is a special keyword for any databases, but there is no stopping anyone from calling themselves a DJ NULL, or have the word NULL because it means differently in another language.

Any ideas on how to resolve this? Any suggestions welcomed. Thank you!

or probably there is a way to do it with mysql itself... there was similar question asked sday: stackoverflow.com/questions/44889613/… — Sundeep
– Sundeep, Commented Jul 4, 2017 at 10:25
Yeah, for example use SELECT COALESCE(name, "") to convert NULLs to an empty string. — Tom Fenech
– Tom Fenech, Commented Jul 4, 2017 at 10:28

Ed Morton · Accepted Answer · 2017-07-04 14:22:13Z

2

All you need is:

$ sed 's/|[[:space:]]*NULL[[:space:]]*|/|  |/g; s/|[[:space:]]*NULL[[:space:]]*|/|  |/g' file
| id | name | nickname | origin | date |
| 1  | Joe  | Mini-J   | BRAZIL |  |
| 2  | Dees | DJ Null Bee| US| 2017-04-01 |
| 3  | NULL AND VOID | NULLZIET |  | 2016-05-13 |
| 4  | Pablo | ALA PUHU MINULLE | GERMANY| 2017-02-14 |

That will work in any POSIX sed.

answered Jul 4, 2017 at 14:22

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dat789 Over a year ago

Thank you for this. The idea that I had to do this twice, as you described, was fitting. Instead of pipe-space*NULLspace-pipe, I wanted to point out for those having tab delimiter (non-printable character), when writing out the above replace [[:space:]] with CTRL+V then TAB key to insert a tab.

Raman Sailopal · Accepted Answer · 2017-07-04 10:49:58Z

0

Use awk:

awk -F\| '{ for (i=2;i<=NF;i++) { if ( $i == " NULL " ) { printf "| " } else if ( $i == " NULL" ) { printf "| DJ Null Bee " } else { printf "|"$i } } printf "\n" }' filename

Using pipe as the field separator, go through each field and then check if the field equates to " NULL " If it does, print nothing. Then check if the field equals " NULL" If it does print "DJ Null Bee" else print the field as is.

answered Jul 4, 2017 at 10:49

Raman Sailopal

13k2 gold badges15 silver badges21 bronze badges

Comments

stefan123t · Accepted Answer · 2017-07-04 11:00:04Z

0

$ cat mysql.txt | sed -r 's/(\| )NULL( \|)/\1\2/g'
| id | name | nickname | origin | date |
| 1  | Joe  | Mini-J   | BRAZIL |  |
| 2  | Dees | DJ Null Bee| US| 2017-04-01 |
| 3  | NULL AND VOID | NULLZIET | NULL| 2016-05-13 |
| 4  | Pablo | ALA PUHU MINULLE | GERMANY| 2017-02-14 |

will only remove capital NULL fields delimited by the opening and closing pipe symbols alone.

edited Jul 4, 2017 at 11:00

answered Jul 4, 2017 at 10:16

stefan123t

3533 silver badges7 bronze badges

4 Comments

Tom Fenech Over a year ago

You could also use ` *` or [[:blank:]]* on either side to make the whitespace inside the pipes optional.

user743382 Over a year ago

It drops the delimiters when it shouldn't (trivially fixed), and it only changes the first NULL in | NULL | NULL | (not so easy to fix)

123 Over a year ago

\| isn't a literal | unless you use the -r/E flag. Also why are you escaping spaces.

stefan123t Over a year ago

removed escapes from spaces, keeping delimiters using \1 and \2.

Claes Wikner · Accepted Answer · 2017-07-04 14:33:10Z

0

awk '{sub(/BRAZIL \| NULL/,"BRAZIL \| ")sub(/NULLZIET \| NULL/,"DJ Null Bee\| ")}1' file

| id | name | nickname | origin | date |
| 1  | Joe  | Mini-J   | BRAZIL |  |
| 2  | Dees | DJ Null Bee| US| 2017-04-01 |
| 3  | NULL AND VOID | DJ Null Bee| | 2016-05-13 |
| 4  | Pablo | ALA PUHU MINULLE | GERMANY| 2017-02-14 |

edited Jul 4, 2017 at 14:33

answered Jul 4, 2017 at 14:27

Claes Wikner

1,5271 gold badge9 silver badges8 bronze badges

Collectives™ on Stack Overflow

Sed remove NULL but only when the NULL means empty or no value

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related