英文:
Regex algorithm to filter complex strings
问题
考虑到这个 SQL 字符串（无效的 SQL）：
SELECT * FROM venue AS v, date AS d
WHERE "v"."venueId" = '1'
AND "v"."<PREFIX>d.dateId" = '102'
AND "v"."<PREFIX>d.dateTime" = '12345678'
LIMIT 5;
我们如何通过将"v"."<PREFIX> 转换为 "d.dateId" 来修复无效的 SQL：
"v"."<PREFIX>已被移除- 可以上面插入一个唯一的 PREFIX（例如）来帮助过滤
期望的有效 SQL：
SELECT * FROM venue AS v, date AS d
WHERE "v"."venueId" = '1'
AND "d"."dateId" = '102'
AND "d"."dateTime" = '12345678'
LIMIT 5;
或者备用的有效 SQL（在 WHERE 条件中没有用于列alias.columnName的双引号）：
SELECT * FROM venue AS v, date AS d
WHERE v.venueId = '1'
AND d.dateId = '102'
AND d.dateTime = '12345678'
LIMIT 5;
英文:
Consider this SQL string (Invalid SQL)
SELECT * FROM venue AS v, date AS d
WHERE "v"."venueId" = '1'
AND "v"."<PREFIX>d.dateId" = '102'
AND "v"."<PREFIX>d.dateTime" = '12345678'
LIMIT 5;
How can we fix the invalid SQL by transforming e.g.
"v"."<PREFIX>d.dateId" into "d.dateId"
"v"."<PREFIX>was removed- An unique PREFIX can be inserted above (example) to help with the filtering
Expected valid SQL:
SELECT * FROM venue AS v, date AS d
WHERE "v"."venueId" = '1'
AND "d"."dateId" = '102'
AND "d"."dateTime" = '12345678'
LIMIT 5;
Or alternative valid SQL (without double quotes for column alias.columnName in WHERE conditions)
SELECT * FROM venue AS v, date AS d
WHERE v.venueId = '1'
AND d.dateId = '102'
AND d.dateTime = '12345678'
LIMIT 5;
答案1
得分: 1
以下是翻译好的部分：
你不真的需要在匹配中包含这个前缀，因为匹配涉及到第二对引号，其中包括一个点：
const badsql = `SELECT * FROM venue AS v, date AS d
WHERE "v"."venueId" = '1'
AND "v"."d.dateId" = '102'
AND "v"."d.dateTime" = '12345678'
LIMIT 5;`;
const sql = badsql.replace(/"[^"]+"\.("[^."]+)\./g, '$1"."');
console.log(sql);
英文:
You don't really need to have that prefix as the match concerns a second pair of quotes that includes a point:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->
const badsql = `SELECT * FROM venue AS v, date AS d
WHERE "v"."venueId" = '1'
AND "v"."d.dateId" = '102'
AND "v"."d.dateTime" = '12345678'
LIMIT 5;`;
const sql = badsql.replace(/"[^"]+"\.("[^."]+)\./g, '$1"."');
console.log(sql);
<!-- end snippet -->
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论