sqlparse()

Read(2794) Label: sql query, split, sequence,

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()

17

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.