0

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!

dat789
  • 1,923
  • 3
  • 20
  • 26
  • Use awk or similar that understand fields. – 123 Jul 04 '17 at 10:16
  • or probably there is a way to do it with mysql itself... there was similar question asked sday: https://stackoverflow.com/questions/44889613/replace-an-entire-field-value-in-a-file-using-awk-or-other – Sundeep Jul 04 '17 at 10:25
  • Yeah, for example use `SELECT COALESCE(name, "")` to convert NULLs to an empty string. – Tom Fenech Jul 04 '17 at 10:28

4 Answers4

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.

You have to do the substitution twice because each match consumes all of the characters in the match so when you have | NULL | NULL | the middle | is consumed by the match on | NULL | and so all that's left is NULL | which does not match | NULL |, so you need 2 passes to match every | NULL |.

Ed Morton
  • 188,023
  • 17
  • 78
  • 185
  • 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. – dat789 Jul 05 '17 at 10:04
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.

It will keep your origin column "| NULL|" in the line "| 3 | NULL AND VOID | DJ Null Bee| NULL| 2016-05-13 |" as well.

stefan123t
  • 183
  • 2
  • 5
  • 1
    You could also use ` *` or `[[:blank:]]*` on either side to make the whitespace inside the pipes optional. – Tom Fenech Jul 04 '17 at 10:22
  • 3
    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) –  Jul 04 '17 at 10:24
  • `\|` isn't a literal `|` unless you use the `-r/E` flag. Also why are you escaping spaces. – 123 Jul 04 '17 at 10:33
  • removed escapes from spaces, keeping delimiters using \1 and \2. – stefan123t Jul 04 '17 at 10:56
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.

Raman Sailopal
  • 12,320
  • 2
  • 11
  • 18
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 |
Claes Wikner
  • 1,457
  • 1
  • 9
  • 8