我從我管理的服務器記錄了大量 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;
注意：新行是膚淺的，只是為了可讀性而添加的
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/377481.html
標籤：javascript sql-server regex tsql deno
上一篇：SQL查詢結果上的SED