將T-SQL陳述句轉換為JavaScriptRegExp時有哪些問題

我從我管理的服務器記錄了大量 T-SQL 陳述句。我試圖將它們歸結為每個實體。

這是其中之一：

SELECT TBLLANGUAGE.NAME AS NAME1, TBLLANGUAGE_1.NAME AS NAME2, 
TBLLANGUAGELANGUAGE.LNGFKCHILD, TBLLANGUAGELANGUAGE.LNGFKPARENT, 
TBLLANGUAGELANGUAGE.STYLE, TBLLANGUAGELANGUAGE.EXTENT, 
TBLLANGUAGELANGUAGE.NATURE, TBLSOURCE.TXTTITLE, TBLSOURCE_1.TXTTITLE AS 
SURTITLE FROM ((((TBLLANGUAGE LEFT JOIN TBLLANGUAGELANGUAGE ON 
TBLLANGUAGE.ID = TBLLANGUAGELANGUAGE.LNGFKPARENT) LEFT JOIN TBLLANGUAGE 
AS TBLLANGUAGE_1 ON TBLLANGUAGELANGUAGE.LNGFKCHILD = TBLLANGUAGE_1.ID) 
LEFT JOIN TBLLANGLANGSOURCE ON TBLLANGUAGELANGUAGE.IDLANGLINK = 
TBLLANGLANGSOURCE.LNGFKLANGLINK) LEFT JOIN TBLSOURCE ON 
TBLLANGLANGSOURCE.LNGFKSOURCE = TBLSOURCE.IDSOURCE) LEFT JOIN TBLSOURCE 
AS TBLSOURCE_1 ON TBLSOURCE.LNGPARTOF = TBLSOURCE_1.IDSOURCE WHERE 
(((TBLLANGUAGELANGUAGE.LNGFKPARENT) = 8687)) OR 
(((TBLLANGUAGELANGUAGE.LNGFKCHILD) = 8687)) ORDER BY 
IIF(TBLLANGUAGELANGUAGE.LNGFKPARENT = 8687,'B','A'), TBLLANGUAGE.NAME, 
TBLLANGUAGE_1.NAME;

我想將其轉換為 JavaScript RegExp，將數字的運行\d和撇號之間的內容替換為'.*'.

到目前為止，我對 Deno 已經做到了這一點：

function getPattern(text: string): string {
  text = text.replace(/\(/g, "\\x28")
    .replace(/\)/g, "\\x29")
    .replace(/\$/g, "\\x24")
    .replace(/\^/g, "\\x5e")
    .replace(/\./g, "\\x2e")
    .replace(/\*/g, "\\x2a")
    .replace(/\[/g, "\\x5b")
    .replace(/\]/g, "\\x5d")
    .replace(/\?/g, "\\x3f");

  [ "\\<\s\\>", "\\<", "\\<=", "=", "\\>=", "\\>"].forEach((op) => {
    const numberPattern = new RegExp(`\\s${op}\\s(\\d )`, "g");
    text.match(numberPattern)?.forEach((e) => {
      text = text.replace(e, ` ${op} \\d `);
    });
  });

  //const textPattern = /'[^']*'\s/g;
  const textPattern = /\s*'.*'\s*/g;
  text.match(textPattern)?.forEach((e) => {
    //const eLength = e.length;
    text = text.replace(e, "\\s*'.*'\\s*");
  });

  return text; //.replace(/\</g, "\\x3c")
    //.replace(/\>/g, "\\x3e");
}

這將上述陳述句呈現為

SELECT TBLLANGUAGE\x2eNAME AS NAME1, TBLLANGUAGE_1\x2eNAME AS NAME2, 
TBLLANGUAGELANGUAGE\x2eLNGFKCHILD, TBLLANGUAGELANGUAGE\x2eLNGFKPARENT, 
TBLLANGUAGELANGUAGE\x2eSTYLE, TBLLANGUAGELANGUAGE\x2eEXTENT, 
TBLLANGUAGELANGUAGE\x2eNATURE, TBLSOURCE\x2eTXTTITLE, 
TBLSOURCE_1\x2eTXTTITLE AS SURTITLE FROM \x28\x28\x28\x28TBLLANGUAGE 
LEFT JOIN TBLLANGUAGELANGUAGE ON TBLLANGUAGE\x2eID = 
TBLLANGUAGELANGUAGE\x2eLNGFKPARENT\x29 LEFT JOIN TBLLANGUAGE AS 
TBLLANGUAGE_1 ON TBLLANGUAGELANGUAGE\x2eLNGFKCHILD = 
TBLLANGUAGE_1\x2eID\x29 LEFT JOIN TBLLANGLANGSOURCE ON 
TBLLANGUAGELANGUAGE\x2eIDLANGLINK = 
TBLLANGLANGSOURCE\x2eLNGFKLANGLINK\x29 LEFT JOIN TBLSOURCE ON 
TBLLANGLANGSOURCE\x2eLNGFKSOURCE = TBLSOURCE\x2eIDSOURCE\x29 LEFT JOIN 
TBLSOURCE AS TBLSOURCE_1 ON TBLSOURCE\x2eLNGPARTOF = 
TBLSOURCE_1\x2eIDSOURCE WHERE 
\x28\x28\x28TBLLANGUAGELANGUAGE\x2eLNGFKPARENT\x29 = \d \x29\x29 OR 
\x28\x28\x28TBLLANGUAGELANGUAGE\x2eLNGFKCHILD\x29 = \d \x29\x29 ORDER 
BY IIF\x28TBLLANGUAGELANGUAGE\x2eLNGFKPARENT = \d ,\s*'.*'\s*\x29, 
TBLLANGUAGE\x2eNAME, TBLLANGUAGE_1\x2eNAME;

我正在將各種組件轉換為它們的\xnn形式，因為我閱讀檔案的方式顯然new RegExp()不夠聰明，無法看到嵌入式，(并且認為我在正則運算式中定義了一個組。也就是說，僅僅說似乎還不夠

const pattern = new RegExp("SELECT TBLLANGUAGE.NAME (etcetera)","gi");

Am I reading the docs wrong and is there a better way? And no, I don't want to write a T-SQL parser unless there's a really, really good reason.

SOMETIME LATER

I've essentially solved my problem, and by using a different pattern matching approach. Please see Extracting example SQL statements from a log up on DEV.

uj5u.com熱心網友回復：

我不完全理解你想要實作的目標，但如果它是：

將此 SQL 陳述句轉換為可以找到其他類似 SQL 的有效正則運算式

然后這樣做：

var sql = `SELECT TBLLANGUAGE.NAME AS NAME1, TBLLANGUAGE_1.NAME AS NAME2, 
TBLLANGUAGELANGUAGE.LNGFKCHILD, TBLLANGUAGELANGUAGE.LNGFKPARENT, 
TBLLANGUAGELANGUAGE.STYLE, TBLLANGUAGELANGUAGE.EXTENT, 
TBLLANGUAGELANGUAGE.NATURE, TBLSOURCE.TXTTITLE, TBLSOURCE_1.TXTTITLE AS 
SURTITLE FROM ((((TBLLANGUAGE LEFT JOIN TBLLANGUAGELANGUAGE ON 
TBLLANGUAGE.ID = TBLLANGUAGELANGUAGE.LNGFKPARENT) LEFT JOIN TBLLANGUAGE 
AS TBLLANGUAGE_1 ON TBLLANGUAGELANGUAGE.LNGFKCHILD = TBLLANGUAGE_1.ID) 
LEFT JOIN TBLLANGLANGSOURCE ON TBLLANGUAGELANGUAGE.IDLANGLINK = 
TBLLANGLANGSOURCE.LNGFKLANGLINK) LEFT JOIN TBLSOURCE ON 
TBLLANGLANGSOURCE.LNGFKSOURCE = TBLSOURCE.IDSOURCE) LEFT JOIN TBLSOURCE 
AS TBLSOURCE_1 ON TBLSOURCE.LNGPARTOF = TBLSOURCE_1.IDSOURCE WHERE 
(((TBLLANGUAGELANGUAGE.LNGFKPARENT) = 8687)) OR 
(((TBLLANGUAGELANGUAGE.LNGFKCHILD) = 8687)) ORDER BY 
IIF(TBLLANGUAGELANGUAGE.LNGFKPARENT = 8687,'B','A'), TBLLANGUAGE.NAME, 
TBLLANGUAGE_1.NAME;`;

// First replace: account for JS regex special chars and escape with backslash to make them literal
// Second replace: get everything between single quotes and make it . ?
// Third replace: get all digit sequences and make them \d 
// Fourth replace: get all whitespace sequences and make them \s 
var sql_regex = sql.replace( /[.* ?^${}()|[\]\\]/g, '\\$&' )
                   .replace( /('. ?')/g, '\'. ?\'' )
                   .replace( /\d /g, '\\d ' )
                   .replace( /\s /g, '\\s ' );

console.log( sql_regex );

// Test if our regex matches the string it was built from
console.log( new RegExp( sql_regex, 'g' ).test( sql ) );

的價值sql_regex：

SELECT\s TBLLANGUAGE\.NAME\s AS\s NAME\d ,\s TBLLANGUAGE_\d \.NAME
\s AS\s NAME\d ,\s TBLLANGUAGELANGUAGE\.LNGFKCHILD,
\s TBLLANGUAGELANGUAGE\.LNGFKPARENT,\s TBLLANGUAGELANGUAGE\.STYLE,
\s TBLLANGUAGELANGUAGE\.EXTENT,\s TBLLANGUAGELANGUAGE\.NATURE,
\s TBLSOURCE\.TXTTITLE,\s TBLSOURCE_\d \.TXTTITLE\s AS\s SURTITLE
\s FROM\s \(\(\(\(TBLLANGUAGE\s LEFT\s JOIN\s TBLLANGUAGELANGUAGE\s ON
\s TBLLANGUAGE\.ID\s =\s TBLLANGUAGELANGUAGE\.LNGFKPARENT\)\s LEFT
\s JOIN\s TBLLANGUAGE\s AS\s TBLLANGUAGE_\d \s ON
\s TBLLANGUAGELANGUAGE\.LNGFKCHILD\s =\s TBLLANGUAGE_\d \.ID\)\s LEFT
\s JOIN\s TBLLANGLANGSOURCE\s ON\s TBLLANGUAGELANGUAGE\.IDLANGLINK\s =
\s TBLLANGLANGSOURCE\.LNGFKLANGLINK\)\s LEFT\s JOIN\s TBLSOURCE\s ON
\s TBLLANGLANGSOURCE\.LNGFKSOURCE\s =\s TBLSOURCE\.IDSOURCE\)\s LEFT
\s JOIN\s TBLSOURCE\s AS\s TBLSOURCE_\d \s ON\s TBLSOURCE\.LNGPARTOF
\s =\s TBLSOURCE_\d \.IDSOURCE\s WHERE
\s \(\(\(TBLLANGUAGELANGUAGE\.LNGFKPARENT\)\s =\s \d \)\)\s OR
\s \(\(\(TBLLANGUAGELANGUAGE\.LNGFKCHILD\)\s =\s \d \)\)\s ORDER\s BY
\s IIF\(TBLLANGUAGELANGUAGE\.LNGFKPARENT\s =\s \d ,'. ?','. ?'\),
\s TBLLANGUAGE\.NAME,\s TBLLANGUAGE_\d \.NAME;

注意：新行是膚淺的，只是為了可讀性而添加的

標籤：javascript sql-server regex tsql deno

上一篇：如何將下表轉換為所需的表

下一篇：如何根據輪班日期回傳結果？