sqlparse()
Description:
Split a SQL query into a sequence.
Syntax:
sql.sqlparse(part)
Note:
The function splits away each statement/clause in a SQL query to generate and returns a sequence. Members of the sequence are ordered like this: SELECT statement, FROM clause (including the JOIN), WHERE clause, GROUP BY statement, HAVING clause and ORDER BY statement. If there is a part parameter, use it to replace the corresponding syntax unit in the original SQL query and return a new SQL.
Parameter:
|
sql |
A SQL query |
|
part |
A SQL string for replacing a statement/clause in the original SQL query; need to work with a certain option |
Option:
|
@s |
SELECT statement |
|
@f |
FROM clause, including the JOIN |
|
@w |
WHERE clause |
|
@g |
GROUP BY statement |
|
@h |
HAVING clause |
|
@o |
ORDER BY statement |
|
@a |
Split each statement/clause into smaller parts; separate WHERE and HAVING clauses according to AND |
Return value:
A sequence or a SQL query
Example:
|
|
A |
|
|
1 |
select emp.EID,emp.NAME,emp.BIRTHDAY,emp.SALARY,dep.DEPT,dep.MANAGER from EMPLOYEE emp left join DEPARTMENT dep on emp.DEPT=dep.DEPT where emp.EID<100 and dep.DEPT='R&D' order by emp.SALARY ,emp.EID |
|
|
2 |
=A1.sqlparse() |
Split each statement/clause away from a SQL query to create a sequence; by default, the returned syntax units include SELECT statement, FROM clause, WHERE clause, GROUP BY statement, HAVING clause and ORDER BY statement; return a non-SQL syntax unit as null. |
|
3 |
=A1.sqlparse@s() |
Split a SQL query to return its SELECT part. |
|
4 |
=A1.sqlparse@f() |
Split a SQL query to return its FROM part. |
|
5 |
=A1.sqlparse@w() |
Split a SQL query to return its WHERE part. |
|
6 |
=A1.sqlparse@o() |
Split a SQL query to return its ORDER BY part. |
|
7 |
=A1.sqlparse@a() |
Re-split a syntax part if it contains multiple syntax units. |
|
8 |
SELECT STUDENTID,sum(SCORE) FROM SCORES group by studentid having sum(score)>460 |
|
|
9 |
=A8.sqlparse@g() |
Split a SQL query to return its GROUP BY part. |
|
10 |
=A8.sqlparse@h() |
Split a SQL query to return its HAVING part. |
|
11 |
select * from dept |
|
|
12 |
=A11.sqlparse() |
|
|
13 |
=A11.sqlparse@s("deptid") |
Replace the SELECT part: * with parameter part: deptid. |
|
14 |
=A11.sqlparse@f("sales") |
Replace the SELECT part: dept with parameter part: sales. |
|
15 |
select EID,NAME,GENDER from ( select * from EMPLOYEE where salary>10000) |
Use the SQL string of the subquery in the FROM clause. |
|
16 |
=A15.sqlparse() |
|
|
SELECT P.LastName, P.FirstName, O.OrderNo FROM Persons P INNER JOIN Orders O ON P.Id_P = O.Id_P ORDER BY P.LastName |
|
|
|
18 |
=A17.sqlparse() |
The FROM clause contains a JOIN.
|
