☰

正規表示式 - 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 是預設設置，因此不需要明確定義它們。

相關內容