I have a large number of T-SQL statements logged from a server I manage. I’m trying to boil them down to one instance of each.
Here’s one of them:
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;
I want to convert that to a JavaScript RegExp, substituting runs of digits for d
and stuff between apostrophes into '.*'
.
So far I’ve got this far with 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"); }
This renders the above statement as
SELECT TBLLANGUAGEx2eNAME AS NAME1, TBLLANGUAGE_1x2eNAME AS NAME2, TBLLANGUAGELANGUAGEx2eLNGFKCHILD, TBLLANGUAGELANGUAGEx2eLNGFKPARENT, TBLLANGUAGELANGUAGEx2eSTYLE, TBLLANGUAGELANGUAGEx2eEXTENT, TBLLANGUAGELANGUAGEx2eNATURE, TBLSOURCEx2eTXTTITLE, TBLSOURCE_1x2eTXTTITLE AS SURTITLE FROM x28x28x28x28TBLLANGUAGE LEFT JOIN TBLLANGUAGELANGUAGE ON TBLLANGUAGEx2eID = TBLLANGUAGELANGUAGEx2eLNGFKPARENTx29 LEFT JOIN TBLLANGUAGE AS TBLLANGUAGE_1 ON TBLLANGUAGELANGUAGEx2eLNGFKCHILD = TBLLANGUAGE_1x2eIDx29 LEFT JOIN TBLLANGLANGSOURCE ON TBLLANGUAGELANGUAGEx2eIDLANGLINK = TBLLANGLANGSOURCEx2eLNGFKLANGLINKx29 LEFT JOIN TBLSOURCE ON TBLLANGLANGSOURCEx2eLNGFKSOURCE = TBLSOURCEx2eIDSOURCEx29 LEFT JOIN TBLSOURCE AS TBLSOURCE_1 ON TBLSOURCEx2eLNGPARTOF = TBLSOURCE_1x2eIDSOURCE WHERE x28x28x28TBLLANGUAGELANGUAGEx2eLNGFKPARENTx29 = d+x29x29 OR x28x28x28TBLLANGUAGELANGUAGEx2eLNGFKCHILDx29 = d+x29x29 ORDER BY IIFx28TBLLANGUAGELANGUAGEx2eLNGFKPARENT = d+,s*'.*'s*x29, TBLLANGUAGEx2eNAME, TBLLANGUAGE_1x2eNAME;
I’m converting various components to their xnn
forms because the way I’m reading the documentation, apparently new RegExp()
isn’t smart enough to see an embedded (
and not think I’m defining a group in the regular expression. That is, it doesn’t seem to be sufficient simply to say
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.
Advertisement
Answer
I don’t fully understand what you’re trying to achieve but if it’s:
convert this SQL statement into a valid regex which can find other SQL like it
then this would do it:
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 ) );
Value of sql_regex
:
SELECTs+TBLLANGUAGE.NAMEs+ASs+NAMEd+,s+TBLLANGUAGE_d+.NAME s+ASs+NAMEd+,s+TBLLANGUAGELANGUAGE.LNGFKCHILD, s+TBLLANGUAGELANGUAGE.LNGFKPARENT,s+TBLLANGUAGELANGUAGE.STYLE, s+TBLLANGUAGELANGUAGE.EXTENT,s+TBLLANGUAGELANGUAGE.NATURE, s+TBLSOURCE.TXTTITLE,s+TBLSOURCE_d+.TXTTITLEs+ASs+SURTITLE s+FROMs+((((TBLLANGUAGEs+LEFTs+JOINs+TBLLANGUAGELANGUAGEs+ON s+TBLLANGUAGE.IDs+=s+TBLLANGUAGELANGUAGE.LNGFKPARENT)s+LEFT s+JOINs+TBLLANGUAGEs+ASs+TBLLANGUAGE_d+s+ON s+TBLLANGUAGELANGUAGE.LNGFKCHILDs+=s+TBLLANGUAGE_d+.ID)s+LEFT s+JOINs+TBLLANGLANGSOURCEs+ONs+TBLLANGUAGELANGUAGE.IDLANGLINKs+= s+TBLLANGLANGSOURCE.LNGFKLANGLINK)s+LEFTs+JOINs+TBLSOURCEs+ON s+TBLLANGLANGSOURCE.LNGFKSOURCEs+=s+TBLSOURCE.IDSOURCE)s+LEFT s+JOINs+TBLSOURCEs+ASs+TBLSOURCE_d+s+ONs+TBLSOURCE.LNGPARTOF s+=s+TBLSOURCE_d+.IDSOURCEs+WHERE s+(((TBLLANGUAGELANGUAGE.LNGFKPARENT)s+=s+d+))s+OR s+(((TBLLANGUAGELANGUAGE.LNGFKCHILD)s+=s+d+))s+ORDERs+BY s+IIF(TBLLANGUAGELANGUAGE.LNGFKPARENTs+=s+d+,'.+?','.+?'), s+TBLLANGUAGE.NAME,s+TBLLANGUAGE_d+.NAME;
Note: new lines are superficial and were only added for readability