Click here to Skip to main content
12,293,327 members (61,750 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


146 bookmarked

SQL Parser

, 3 Feb 2009 CDDL
Rate this:
Please Sign up or sign in to vote.
This article describes an implementation of a simple SQL parser which can be used to add or modify "where" and "order by" parts of SQL queries (PL/SQL).


Sometimes it is necessary to apply a custom filter to an existing SQL query (to perform search by a custom criteria) or to order the query results depending on the user action (when you work with large amounts of data and display only a small part of it to the user, such as displaying only N first records). I faced this problem while implementing a reusable control for searching. In this control the user should be able to specify a filter and order the search results as he needs. A SQL query is specified in the control’s data source and it can contain any parts including sub-queries. In this case it is not enough just to add a custom ‘where’ clause to the end of the query as it may already contain parts which should follow the where clause according to the SQL syntax. This article describes a simple SQL parser which allows you to add or modify the ‘where’ and ‘order by’ clauses of a SQL query (PL/SQL).


Any document can be separated into tags (special words or specially formatted characters which have some extra meaning; it depends on the document format and task which sequence of character should be treated as a tag), words, separators (points, commas, braces, etc.), and white spaces. Tags can contain other elements such as sub-tags, words, etc. The parser presented in this article separates a text (sequence of characters) into elements mentioned above and builds a tree from them. Later this tree can be restructured, some its nodes can be changed or removed according to the task.

Every tag in the document is represented with a special class (every type of tag has its own class). While parsing a document, the parser reads the document symbol by symbol and determines whether the current sequence of characters is a tag or a simple text. If it is a tag, the parser creates an instance of the class which represents the tag.

Every class which describes a tag is derived from an abstract class named TagBase and has common information about the tag (whether it has contents, its type (string identifier), value, and whether it can be terminated by the end of the document (whether its ending should be specified explicitly)).The parser has a static set of classes (types) which correspond to the set of the tags of the document format (the Tags property). When the parser needs to determine whether there is a tag at the current position of the document, it enumerates through its collection of tag types. For every tag type (element in the collection) it requests a special attribute (derived from the MatchTagAttributeBase class). This attribute has a special method named Match, which returns a value indicating whether this type of tag is located at the specified position in the document. If so, the parser creates an instance of that class. After an instance of a tag class is created, it is converted into an XML node and then added into the XML tree which reflects the structure of the query.

Here is how a tag class declaration looks like:

internal class StringLiteralTag : TagBase
internal class MatchStringLiteralTagAttribute : MatchTagAttributeBase
  public override bool Match(string sql, int position)

Here is the class diagram of the tags used in the SQL parser:


This list of tags does not include all the tags which may be present in a SQL query, it includes only those tags which are necessary for modifying the ‘where’ and ‘order by’ clauses of a query.

Using the Code

To modify a SQL query, you should first create an instance of the SqlParser class and then invoke its Parse method:

SqlParser myParser = new SqlParser();

If you need to modify the ‘where’ clause, you should modify the WhereClause property of the parser:

string myOrginalWhereClause = myParser.WhereClause;
if (string.IsNullOrEmpty(myOrginalWhereClause))
  myParser.WhereClause = myAdditionalWhereClause;
  myParser.WhereClause = string.Format("({0}) AND ({1})", myOrginalWhereClause,

If you need to modify the ‘order by’ clause, you should modify the OrderByClause property of the parser:

string myOrginalOrderByClause = myParser.OrderByClause;
if (string.IsNullOrEmpty(myOrginalOrderByClause))
  myParser.OrderByClause = myAdditionalOrderByClause;
  myParser.OrderByClause = string.Format("{0}, {1}", myOrginalOrderByClause,

After all the necessary modifications, you can get the final SQL query by using the ToText method:


Ways to Make Code Faster

There are a few ways to make the code work faster. When the parser builds a tree, it uses the XmlDocument class. This is useful for debugging as we can save the tree to a file and then look through it with an internet browser or another tool. Also we can search certain nodes with the x-path syntax. But the parser will work faster if we make our own tree-like data structure without redundant functionality.

Also, it may be preferable not to use reflection as it is quite slow compared to other parser operations (the method which returns attributes of a class may be accessed thousands of times when parsing large documents). Instead we can make some data structures which will store metadata about tag classes. It is not so substantial for the SQL parser, but it may be much more substantial for parsers of large documents.

Change Log

  • 1 Feb 2009

    • A new tag which parses quoted identifiers was added.


This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)


About the Author

Sergey Gorbenko
Software Developer (Senior)
Canada Canada
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionOne bug one sample SQL Pin
Member 1011124722-Jun-15 16:24
memberMember 1011124722-Jun-15 16:24 
QuestionOnly OrderByClause and WhereClause? How about SelectClause? Pin
devvvy7-Nov-14 17:36
memberdevvvy7-Nov-14 17:36 
QuestionSpaces in Tokens Pin
Steve Pinckney18-Jun-12 18:38
memberSteve Pinckney18-Jun-12 18:38 
AnswerRe: Spaces in Tokens Pin
Yorik7723-Jan-14 3:20
memberYorik7723-Jan-14 3:20 
GeneralRe: Spaces in Tokens Pin
Steve Pinckney23-Jan-14 5:33
memberSteve Pinckney23-Jan-14 5:33 
GeneralRe: Spaces in Tokens Pin
Yorik7718-Feb-14 21:13
memberYorik7718-Feb-14 21:13 
BugBug when columnname contains SQL keyword name or is between [ ] Pin
Member 807470823-Jan-12 2:46
memberMember 807470823-Jan-12 2:46 
QuestionVery well!! Pin
flavio19661-Dec-11 21:06
memberflavio19661-Dec-11 21:06 
GeneralMy vote of 5 Pin
Kanasz Robert18-Nov-11 5:42
memberKanasz Robert18-Nov-11 5:42 
QuestionAsk At Runtime Pin
rlrook9-Nov-11 7:19
memberrlrook9-Nov-11 7:19 
AnswerRe: Ask At Runtime Pin
Sergey Gorbenko9-Nov-11 14:04
memberSergey Gorbenko9-Nov-11 14:04 
QuestionAdding ColumnsClause and support for paging Pin
martin.nedopil8-Nov-11 2:52
membermartin.nedopil8-Nov-11 2:52 
AnswerRe: Adding ColumnsClause and support for paging Pin
Sergey Gorbenko9-Nov-11 14:24
memberSergey Gorbenko9-Nov-11 14:24 
Questionsimple but still useful Pin
Tako.Lee8-Aug-11 22:57
memberTako.Lee8-Aug-11 22:57 
AnswerRe: simple but still useful Pin
Sergey Gorbenko9-Aug-11 12:44
memberSergey Gorbenko9-Aug-11 12:44 
GeneralMy vote of 5 Pin
aaroncampf19-Apr-11 7:52
memberaaroncampf19-Apr-11 7:52 
Simple and Effective!
GeneralParserBase.ParsedDocument - idenfication of joined tables/sets names? [modified] Pin
devvvy27-Feb-11 15:26
memberdevvvy27-Feb-11 15:26 
GeneralRe: ParserBase.ParsedDocument - idenfication of joined tables/sets names? Pin
Serge Gorbenko27-Feb-11 22:41
memberSerge Gorbenko27-Feb-11 22:41 
GeneralFrom Clause Pin
devvvy28-Feb-11 15:05
memberdevvvy28-Feb-11 15:05 
GeneralRe: From Clause Pin
Steve Pinckney18-Jun-12 18:32
memberSteve Pinckney18-Jun-12 18:32 
GeneralRe: From Clause Pin
forgie200915-Aug-14 3:01
memberforgie200915-Aug-14 3:01 
GeneralMy vote of 5 Pin
waltlounsbury11-Feb-11 11:33
memberwaltlounsbury11-Feb-11 11:33 
GeneralIf you'd a just put the BRACES and QI's INSIDE the Select TAG! Pin
waltlounsbury11-Feb-11 11:27
memberwaltlounsbury11-Feb-11 11:27 
RantRe: If you'd a just put the BRACES and QI's INSIDE the Select TAG! Pin
waltlounsbury11-Feb-11 12:20
memberwaltlounsbury11-Feb-11 12:20 
GeneralRe: If you'd a just put the BRACES and QI's INSIDE the Select TAG! Pin
Serge Gorbenko14-Feb-11 8:58
memberSerge Gorbenko14-Feb-11 8:58 
GeneralRe: If you'd a just put the BRACES and QI's INSIDE the Select TAG! Pin
waltlounsbury14-Feb-11 9:00
memberwaltlounsbury14-Feb-11 9:00 
GeneralSelect Columns Pin
Archimedes2429-Nov-10 10:19
memberArchimedes2429-Nov-10 10:19 
GeneralRe: Select Columns Pin
Serge Gorbenko29-Nov-10 21:36
memberSerge Gorbenko29-Nov-10 21:36 
GeneralRe: Select Columns Pin
waltlounsbury11-Feb-11 11:29
memberwaltlounsbury11-Feb-11 11:29 
QuestionHow to deal with Union/Intersect/Except Pin
carlshe30-Oct-10 1:49
membercarlshe30-Oct-10 1:49 
AnswerRe: How to deal with Union/Intersect/Except Pin
Serge Gorbenko1-Nov-10 3:17
memberSerge Gorbenko1-Nov-10 3:17 
GeneralExcellent and useful piece of analysis code Pin
Cleveland Mark Blakemore7-Oct-10 12:55
memberCleveland Mark Blakemore7-Oct-10 12:55 
GeneralMS SQL Support for column names like [Header:X-Notice-ID] Pin
Mike Lucas26-Aug-10 4:23
memberMike Lucas26-Aug-10 4:23 
GeneralRe: MS SQL Support for column names like [Header:X-Notice-ID] Pin
Serge Gorbenko28-Aug-10 10:50
memberSerge Gorbenko28-Aug-10 10:50 
Questionc/c++ version please? Pin
Martial Spirit11-Aug-10 2:09
memberMartial Spirit11-Aug-10 2:09 
AnswerRe: c/c++ version please? Pin
Serge Gorbenko11-Aug-10 2:11
memberSerge Gorbenko11-Aug-10 2:11 
GeneralSql parser for Oracle Pin
alhambra-eidos2-Aug-10 23:54
memberalhambra-eidos2-Aug-10 23:54 
GeneralRe: Sql parser for Oracle Pin
Serge Gorbenko2-Aug-10 23:57
memberSerge Gorbenko2-Aug-10 23:57 
GeneralRe: Sql parser for Oracle Pin
alhambra-eidos5-Aug-10 1:19
memberalhambra-eidos5-Aug-10 1:19 
GeneralRe: Sql parser for Oracle Pin
Serge Gorbenko5-Aug-10 5:58
memberSerge Gorbenko5-Aug-10 5:58 
GeneralSQL parser Pin
ekhashab6-Jul-10 7:07
memberekhashab6-Jul-10 7:07 
GeneralRe: SQL parser Pin
Serge Gorbenko19-Jul-10 3:58
memberSerge Gorbenko19-Jul-10 3:58 
GeneralGood work. But a small bug Pin
N a v a n e e t h17-May-10 21:03
mvpN a v a n e e t h17-May-10 21:03 
GeneralLittle Bug Pin
cbapiaz26-Apr-10 6:01
membercbapiaz26-Apr-10 6:01 
GeneralRe: Little Bug Pin
Serge Gorbenko29-Apr-10 23:11
memberSerge Gorbenko29-Apr-10 23:11 
GeneralSmall bug Pin
martin.nedopil3-Feb-09 22:37
membermartin.nedopil3-Feb-09 22:37 
GeneralRe: Small bug [modified] Pin
Serge Gorbenko3-Feb-09 23:03
memberSerge Gorbenko3-Feb-09 23:03 
GeneralRe: Small bug Pin
martin.nedopil5-Feb-09 2:27
membermartin.nedopil5-Feb-09 2:27 
GeneralRe: Small bug Pin
martin.nedopil5-Feb-09 4:00
membermartin.nedopil5-Feb-09 4:00 
GeneralRe: Small bug Pin
rhyu21-Jan-10 6:00
memberrhyu21-Jan-10 6:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160518.1 | Last Updated 3 Feb 2009
Article Copyright 2009 by Sergey Gorbenko
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid