正则表达式来查找源和目标

huangapple
go评论117阅读模式
英文:

Regular expression to find source and target

问题

需要帮忙编写通用正则表达式以查找以下情景中的源表：

示例1输出：

  1. xyz.test

示例2输出：无输出，因为sample.Test是目标表而不是源表。

我尝试过下面的正则表达式 \b(?!DELETE\s+FROM\s+)(?:FROM|JOIN)\s+(\w+./*[^\s]+)，它对第一个情景起作用，但对第二个情景预期没有输出，然而我得到了sample.test。

英文:

Need help to write generic regular expression for to find the source table from the below scenarios:

Sample 1:

  1. INSERT INTO a.test
  2. (
  3. test1,
  4. test2
  5. )
  6. select 
  7. test1,
  8. test2
  9. from 
  10. xyz.test

Output 1:

  1. xyz.test

Sample 2:

  1. *******************************************************                                                                                
  2.     MACRO                                                                 
  3.         abc.test
  4.     PURPOSE                                   
  5.          TO DELETE THE DATA FROM abc.test
  6.     INPUT PARAMS                              
  7.                    NONE                                                                      
  8.     CALLED BY                                                                            
  9.         THIS MACRO IS BEING CALLED FROM sql
  10. *******************************************************/ 
  11.     DELETE FROM sample.test

No output for this as sample.Test is target table not source.

I have tried below regex \b(?!DELETE\s+FROM\s+)(?:FROM|JOIN)\s+(\w+./*[^\s]+) it's working for 1st scenario but for 2nd scenario I was expecting no output however I am getting sample.test

答案1

得分: 2

I really don't think that a regular expression is the safe way to go.
There are really too many things to handle, such as comments, unions, etc.

I had a play with the
SQLGlot library with this code:

  1. ##python3 -m pip install sqlglot
  3. from sqlglot import parse_one, exp
  5. queries = [
  1. INSERT INTO a.test
  2. (
  3. test1,
  4. test2
  5. )
  6. select 
  7. test1,
  8. test2
  9. from 
  10. xyz.test
  12. Source tables:
  13. ['xyz.test']
  17. /*******************************************************                                                        
  19.     MACRO
  20.         abc.test
  21.     PURPOSE
  22.          TO DELETE THE DATA FROM abc.test
  23.     INPUT PARAMS
  24.                    NONE
  25.     CALLED BY
  26.         THIS MACRO IS BEING CALLED FROM sql
  27. *******************************************************/
  28.     DELETE FROM sample.test
  30. Source tables:
  31. []
  35. SELECT
  36.   t1.firstname,
  37.   t1.lastname,
  38.   t2.email,
  39.   t3.order
  40. FROM abc.table1 t1
  41. LEFT JOIN abc.table2 t2 ON t2.id = t1.id
  42. LEFT JOIN abc.table3 t3 ON t3.email = t2.email
  44. Source tables:
  45. ['abc.table1 AS t1', 'abc.table2 AS t2', 'abc.table3 AS t3']
  49. SELECT id, name FROM table1
  50. UNION
  51. SELECT id, name FROM table2
  53. Source tables:
  54. ['table1', 'table2']
英文:

I really don't think that a regular expression is the safe way to go.
There are really too many things to handle, such as comments, unions, etc.

I had a play with the
SQLGlot library with this code:

  1. ##python3 -m pip install sqlglot
  3. from sqlglot import parse_one, exp
  5. queries = [
  6. """
  7. INSERT INTO a.test
  8. (
  9. test1,
  10. test2
  11. )
  12. select 
  13. test1,
  14. test2
  15. from 
  16. xyz.test
  17. """,
  18. """
  19. /*******************************************************                                                                                
  20.     MACRO                                                                 
  21.         abc.test
  22.     PURPOSE                                   
  23.          TO DELETE THE DATA FROM abc.test
  24.     INPUT PARAMS                              
  25.                    NONE                                                                      
  26.     CALLED BY                                                                            
  27.         THIS MACRO IS BEING CALLED FROM sql
  28. *******************************************************/ 
  29.     DELETE FROM sample.test
  30. """,
  31. """
  32. SELECT
  33.   t1.firstname,
  34.   t1.lastname,
  35.   t2.email,
  36.   t3.order
  37. FROM abc.table1 t1
  38. LEFT JOIN abc.table2 t2 ON t2.id = t1.id
  39. LEFT JOIN abc.table3 t3 ON t3.email = t2.email
  40. """,
  41. """
  42. SELECT id, name FROM table1
  43. UNION
  44. SELECT id, name FROM table2
  45. """
  46. ]
  48. for query in queries:
  49.     print(query)
  50.     sourceTables = []
  51.     for select in parse_one(query).find_all(exp.Select):
  52.         for table in select.find_all(exp.Table):
  53.             sourceTables.append(str(table))
  54.     print("Source tables:")
  55.     print(sourceTables)
  56.     print("\n")

It outputs the following:

  1. INSERT INTO a.test
  2. (
  3. test1,
  4. test2
  5. )
  6. select 
  7. test1,
  8. test2
  9. from 
  10. xyz.test
  12. Source tables:
  13. ['xyz.test']
  17. /*******************************************************                                                        
  19.     MACRO
  20.         abc.test
  21.     PURPOSE
  22.          TO DELETE THE DATA FROM abc.test
  23.     INPUT PARAMS
  24.                    NONE
  25.     CALLED BY
  26.         THIS MACRO IS BEING CALLED FROM sql
  27. *******************************************************/
  28.     DELETE FROM sample.test
  30. Source tables:
  31. []
  35. SELECT
  36.   t1.firstname,
  37.   t1.lastname,
  38.   t2.email,
  39.   t3.order
  40. FROM abc.table1 t1
  41. LEFT JOIN abc.table2 t2 ON t2.id = t1.id
  42. LEFT JOIN abc.table3 t3 ON t3.email = t2.email
  44. Source tables:
  45. ['abc.table1 AS t1', 'abc.table2 AS t2', 'abc.table3 AS t3']
  49. SELECT id, name FROM table1
  50. UNION
  51. SELECT id, name FROM table2
  53. Source tables:
  54. ['table1', 'table2']

Seems to do what you wanted...

答案2

得分: 0

以下将捕获来自所提供示例的值。

注意，要求“delete”，“from”或“join”是行上第一个非空白字符序列。

  1. (?is)^\s*(?:delete\s+)?(?:from|join)\s*.*?([^\s]+)

(?is) 将启用_忽略大小写_ 和 单行 模式。

输出

  1. xyz.test
  2. sample.test
英文:

The following will capture the values from both of the provided examples.

As a note, a requirement is that the "delete", "from", or "join" are the first non-white-space character sequences on the line.

  1. (?is)^\s*(?:delete\s+)?(?:from|join)\s*.*?([^\s]+)

The (?is) will toggle-on the ignore case, and single-line modes.

Output

  1. xyz.test
  2. sample.test

huangapple
  • 本文由 发表于 2023年6月1日 19:01:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/76381207.html
  • python
  • regex
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定