☰

正则表达式——SQL操作

tag-icon • tag-icon linux text-processing regular-expression sql
正则表达式——SQL操作 
[pol@fedora data]$ lsb_release -a
LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID: Fedora
Description:    Fedora release 34 (Thirty Four)
Release:    34
Codename:   ThirtyFour

我正在尝试将示例数据库文件从 MS SQL Server 转换为 PostgreSQL。

所以，我有两个无法解决的小问题。

shipname       NVARCHAR(40) NOT NULL,

那是

  • （总是) 两个空格

  • 标识符（即字段名称） - 始终 [az] - 小写字母

  • 后跟未知数量的空格

  • 后跟 NVARCHAR(xy) NOT NULL或者它后面可能是 NVARCHAR(xy) NULL

我想把它变成

shipname       TEXT NOT NULL CHECK (LENGTH(shipname)  <= xy),

或者

shipname       TEXT NULL,

到目前为止我所拥有的：

sed 's/^  [a-z]+[ ]+NVARCHAR([0-9]+) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g'

所以，

  • ^是字符串的开头

  • 后面跟两个空格

  • 后面是我的字段名称 [az]+

  • 后面跟着一个任意的编号。空格 [ ]+

  • NVARCHAR([0-9]+)

并代入

TEXT后跟 NOT NULL，然后 CHECK(LENGTH(xy) - 反向引用 1 - <= 反向引用 2...

我已经尝试了上述的各种排列和组合，但似乎没有什么对我有用。

[pol@fedora data]$ sed 's/^  [a-z]+[ ]+NVARCHAR([0-9]+) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g' 
sed: -e expression #1, char 87: invalid reference \2 on `s' command's RHS

获取无效的反向引用...

理想情况下，我强调理想地，如果 NVARCHAR(xy) 后面的字符串是NULL并且不是 NOT NULL，我不想进行任何长度检查 - 因为采用 NULL 的长度是没有意义的...这是条件行为 - 不确定在正则表达式中是否可能......

ps 认为这是微不足道的。

有这样的数据：

N'Strada Provinciale 1234', N'Reggio Emilia', NULL, N'10289', N'Italy');

我想将 更改N'为简单的撇号'（这N'是 SQL Server 的事情），但我不想将 更改NULL为空字符串，或更糟ULL- 所以我尝试：

[pol@fedora data]$ sed 's/N\'\'/g TSQLV5.sql

但得到

sed: -e expression #1, char 7: unterminated `s' command

我知道我已经使用了sed很多，但愿意接受任何awk可以执行所需任务的命令。

答案1

既然你使用fedora了，GNU sed那么这应该可以工作：

s="  shipname       NVARCHAR(40) NOT NULL,"
echo "$s" | sed -E '/NOT/{s/^  ([[:lower:]]+)\s*NVARCHAR\(([[:digit:]]+)\) NOT NULL,$/\1 TEXT NOT NULL CHECK \(LENGTH\(\1\) <= \2\),/;q0} ; s/^  ([[:lower:]]+)/\1 TEXT NULL,/'

这模拟了一个假的 if。

if:

在 db 结构中找到a NOT( )，然后执行第一个 sed 命令，然后退出 ( ) 而不执行第二条语句。/NOT/q0

else:

没有NOT找到关键字，执行第二个实例。

对于第二个要求：

sed "s/N'/'/g"

全局搜索N'并将其替换为 only '。我发现'与命令行分隔"符交换很有用sed，可以使其更干净，而无需进行大量转义。

将第一个放入sed文件中：

#!/bin/sed -Ef

# If a NOT is found execute this:
# capture the column name and the value of this
/NOT/ {
    s/^  ([[:lower:]]+)\s*NVARCHAR\(([[:digit:]]+)\) NOT NULL,$/\1 TEXT NOT NULL CHECK \(LENGTH\(\1\) <= \2\),/

    # Quit without execute the other statement
    q0
}

# Else: If we are here then the database
# structure does not contains a length for the column;
# so it should be NULL
s/^  ([[:lower:]]+)/\1 TEXT NULL,/

该{命令用于将更多sed命令组合在一起。

这q是quit命令，用于sed退出。sed如果第一次测试成功，我在这里使用它在遇到最后一行之前强制退出。

答案2

您已经得到了答案，但我想添加您自己的方法中出现的问题，这样您就可以从中学习，而不仅仅是复制一些解决方案：

  • 您使用扩展正则表达式，但忘记提供该-E选项sed。
  • 您想要重用该标识符，但没有将其括在()
  • 您似乎将 ERE()组与文字组混合在一起。你可能的意思是sed -E 's/^ ([a-z]+)[ ]+NVARCHAR\(([0-9]+)\) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g'
  • 直到空格的第一部分不会在替换中显示。您还需要将其分组并在替换时将其用作参考：sed -E 's/^( ([a-z]+)[ ]+)NVARCHAR\(([0-9]+)\) NOT NULL/\1TEXT NOT NULL CHECK \(LENGTH\((\2) <= (\3)\)/g'
  • [ ]+与 相同+。这不是错误，但会使阅读更加混乱。
  • 该g选项是多余的。对于像^或那样的锚点，$不可能进行多次替换。
  • 您可以通过设置NOT可选选项来避免多个表达式： `sed -E 's/^( ([az]+) +)NVARCHAR(([0-9]+)) (NOT )?NULL/\1TEXT \4NULL CHECK (长度((\2) <= (\3))/'
  • 另一方面，如果您想省略检查，可以通过单独的替换来实现：s/^( [a-z]+ +)NVARCHAR\(([0-9]+)\) NULL/\1TEXT NULL/
  • 您s/N\'\'/g错过了搜索模式和替换之间的分隔符：s/N\'/\'/g

所以你最终会得到

sed -E 's/^(  ([a-z]+) +)NVARCHAR\(([0-9]+)\) NOT NULL/\1TEXT NOT NULL CHECK \(LENGTH\((\2) <= (\3)\)/
  s/^(  [a-z]+ +)NVARCHAR\(([0-9]+)\) NULL/\1TEXT NULL/
  s/N\'/\'/g'

答案3

sed对于某些任务来说非常有用，但其他一些任务需要功能齐全的语言，例如awkor perl，带有条件和 printf 等。最好是一种读起来不像正则表达式和 RPN 计算器的可怕混合体的语言:-)。

#!/usr/bin/perl
use strict;

while(<>) {
  # print verbatim any lines that don't define an identifier
  unless (m/^\s+\S/) { print; next };
  # print a blank line before certain identifiers
  print "\n" if m/birthdate|address|phone/;

  # various regex transformations for IDENTITY and VARCHAR fields
  s/\s+NOT NULL IDENTITY/ GENERATED BY DEFAULT AS IDENTITY/;
  s/([[:lower:]]+)\s+NVARCHAR\((\d+)\) NOT NULL/$1 TEXT NOT NULL CHECK (LENGTH($1) <= $2)/;
  s/\s+NVARCHAR\((\d+)\)\s+NULL/ TEXT NULL/;

  # remove length checks from NULL definitions
  s/\s+CHECK.*/,/ if /(?<!NOT) NULL/;

  # add a comma at the end of the mgrid line if it's not there
  s/\s*$/,/ if /mgrid/ && ! /,\s*$/;

  # hacky crap to nicely format "TYPE (NOT )?NULL" output.
  my @F = split;
  my $identifier = shift @F;
  my $type = shift @F;
  $type .= " " . shift @F if ($F[0] =~ /NOT/);
  $type = sprintf "%-8s", $type;
  $type .= " " . shift @F if ($F[0] =~ /NULL/);

  printf "  %-15s %-13s%s\n", $identifier, $type, join(" ",'',@F);

  # print the test_field definition after mgrid
  if ($identifier eq 'mgrid') {
    print "  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)\n";
  };
}

  • 这是一种将输入转换为（大致）所需输出的相当强力的方法。一些正则表达式转换，以及一些代码来很好地排列“字段”。以及一些额外的打印语句，用于在适当的位置添加空行和 test_field。因此，它不是一般有用，但可以根据需要进行调整以适应其他 SQL 转换。

  • 该脚本实现了问题中的描述，而不是“所需输出”中显示的内容（因此，例如， 和 都region没有postalcode长度检查，因为它们是 NULL 字段）。

输出：

CREATE TABLE employee
(
  empid           INT           GENERATED BY DEFAULT AS IDENTITY,
  lastname        TEXT NOT NULL CHECK (LENGTH(lastname) <= 20),
  firstname       TEXT NOT NULL CHECK (LENGTH(firstname) <= 10),
  title           TEXT     NULL,
  titleofcourtesy TEXT     NULL,

  birthdate       DATE NOT NULL,
  hiredate        DATE NOT NULL,

  address         TEXT NOT NULL CHECK (LENGTH(address) <= 60),
  city            TEXT NOT NULL CHECK (LENGTH(city) <= 15),
  region          TEXT     NULL,
  postalcode      TEXT     NULL,
  country         TEXT NOT NULL CHECK (LENGTH(country) <= 15),

  phone           TEXT NOT NULL CHECK (LENGTH(phone) <= 24),
  mgrid           INT      NULL,
  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)

);

以下是脚本输出与所需输出的差异（清理以删除注释和一些无关的空格字符后）：

-  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
-  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
+  region          TEXT     NULL,
+  postalcode      TEXT     NULL,
其他的建议：

  • 你可能PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY想要empid

  • postgresql 有一个 VARCHAR(n) 数据类型，它可能比 TEXT 更合适，并且转换起来更简单：s/NVARCHAR/VARCHAR/。 VARCHAR 具有固定长度，因此 a) 不需要长度约束检查，b) 索引和搜索速度更快。

  • 允许字段为 NULL 是默认设置，因此不需要明确定义它们。

相关内容