Sed删除NULL，但仅当NULL表示为空或没有值时 [英] Sed remove NULL but only when the NULL means empty or no value

问题描述

从MySQL导出一个表，其中没有任何值的字段中将包含关键字NULL.

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 |

我编写了一个脚本，使用单行sed自动删除所有出现的NULL，它将正确删除date列中的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 |

显然，将删除全局搜索和替换所有出现的NULL，甚至"ALA PUHU MINULLE"也将变为"ALA PUHU MIE"，这是不正确的.

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.

我想使用正则表达式可能对应用规则有用吗?但是，如果是这样，"DJ Null Bee"会受到影响还是会变成"DJ Bee"?预期的结果应该确实是:

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 |

鉴于NULL是任何数据库的特殊关键字，但是并不能阻止任何人自称DJ NULL或使用NULL这个词，因为它在另一种语言中的含义有所不同.

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!

推荐答案

您需要的是:

$ 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 |

这将适用于任何POSIX版本.

That will work in any POSIX sed.

您必须进行两次替换，因为每个匹配项都消耗了匹配项中的所有字符，因此当您具有 |时，NULL |NULL | 中间的 | 被 |上的匹配所消耗NULL | ，因此剩下的就是 NULL | ，它与 |不匹配NULL | ，因此您需要2次传递才能匹配每个 |NULL | .

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 |.

这篇关于Sed删除NULL，但仅当NULL表示为空或没有值时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！