How to parse out all columns out of a sql using regular expression?

Question

I want to parse out all the columns out of a sql using regular expression, but no idea about parsing sql coming with functions.Like :

 SELECT
   NVL(SUM(MISSIONNO), 0) DISTCNT, COUNT(DISTINCT(USERID)) USERIDCNT
  FROM EVTSUPPLYHIST
 WHERE EVTCODE = #evtCode#
   AND EVTNO = #evtNo#
   AND GIFTCODE = #giftCode#
   AND SUPPLYDT BETWEEN TO_DATE(#startDate#, 'YYYY/MM/DD HH24:MI:SS') 
   AND TO_DATE(#endDate#, 'YYYY/MM/DD HH24:MI:SS')

For this sql above , right results should be DISTCNT and USERIDCNT, as you might guess it's not pure and clean sql, but ibatis-based paremetered sql.Any ideas?

I don't think there's a magic bullet, SQL is a language, parsing languages requires writing parsers. I'm sure if you search around, you'll find an SQL parser written in Java. Note that since SQL isn't a regular language, you will not be able to reliably parse it using regular expressions. — T.J. Crowder
– T.J. Crowder, Commented Feb 27, 2012 at 6:57
It's not so easy. And you can't do that only with regex because of many nested parentheses. — shift66
– shift66, Commented Feb 27, 2012 at 6:59
Maybe as a rough first approximation you can look for words preceding a comma or preceding a 'FROM' (after removing new lines from the query) consisting of [a-zA-Z_]? If the query is SELECT COUNT(USERID) FROM USER_INFO what do you want to return? I suppose you could also allow for multiple words in a column name if it was surrounded by back ticks ("SELECT user id FROM ..") — mathematical.coffee
– mathematical.coffee, Commented Feb 27, 2012 at 7:00
I'll do some research on sql parser, but unfortunately ,my sql is not pure and clean sql, that is it's parametered sql in a flavor of ibatis, not sure if it's ok with that, it's worth a research. I've reedited the thread and paste the full sql. thanks — user1231111
– user1231111, Commented Feb 28, 2012 at 2:00

Andreas Dolk · Accepted Answer · 2012-02-27 07:03:34Z

3

I'd recommend using one of the many SQL expression parsers, like zql. From their documentation I see, that they create a java objects from an SQL expression and it should be quite trivial to get the column names from that datastructure.

answered Feb 27, 2012 at 7:03

Andreas Dolk

115k20 gold badges185 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1231111 Over a year ago

I tried zql, but seems like it's not well-documented, then I gave up.

Andreas Dolk Over a year ago

They provide some good examples, took me a few minutes to parse a sql query string, but - it will not read your special sql dialect. (unrecognized function NVL) One would have to adapt the JavaCC input files

Big Bad Baerni · Accepted Answer · 2012-02-27 08:00:54Z

1

If you can wait after you've executed the query you may get all the informations very easily from the returned ResultSet.

ResultSetMetaData meta = rs.getMetaData();
int colCount = meta.getColumnCount();

for (int i = 0; i < colCount; i++) {
  String colName = meta.getColumnName(i + 1);
}

answered Feb 27, 2012 at 8:00

Big Bad Baerni

9967 silver badges21 bronze badges

1 Comment

user1231111 Over a year ago

If it's executed through jdbc, it's totally right. But my scenario is I need get the information without jdbc execution. I'm working on a code generation tool which can generate vo according to the sql.

Collectives™ on Stack Overflow

How to parse out all columns out of a sql using regular expression?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related