Regular expression - Pattern and matcher

[avishek bose]

hi,

I want to use a regular expression in java to get the column names from a query string -

ex- select NVL(SUM(EMP_SAL), 0), MAX(EMP_AGE), EMP_NAME, DEPT_NAME, NVL(DEPT_ID, "") from EMP_TABLE

Can anyone help me with the regular expression(to be used in pattern and matcher ) to capture only the column names like EMP_SAL, EMP_AGE, EMP_NAME, DEPT_NAME, DEPT_ID


Regards

[Vikas Solegaonkar]

Could not get anything better than this.

Pattern P = Pattern.compile("select (.*) from");
Pattern P1 = Pattern.compile(" ([^,\\(\\)]+)[, ]");
Pattern P2 = Pattern.compile("\\(([^\\(\\),]+)[^\\(\\)]*\\)");
String S = "select NVL(SUM(EMP_SAL), 0), MAX(EMP_AGE), EMP_NAME, DEPT_NAME, NVL(DEPT_ID, \"\") from EMP_TABLE";
Matcher M = P.matcher(S);
M.find();
Matcher M1 = P1.matcher(M.group(1));
Matcher M2 = P2.matcher(M.group(1));
while(M1.find()){
	System.out.println(M1.group(1));
}
while(M2.find()) {
	System.out.println(M2.group(1));
}

Output:

EMP_NAME
DEPT_NAME
EMP_SAL
EMP_AGE
DEPT_ID

You can of course merge the three patterns into one.. but the expression will be too ugly to look at.

[Winston Gutkowski]

avishek dasdsa wrote:I want to use a regular expression in java to get the column names from a query string -

ex- select NVL(SUM(EMP_SAL), 0), MAX(EMP_AGE), EMP_NAME, DEPT_NAME, NVL(DEPT_ID, "") from EMP_TABLE

I guess my question is why? If the statement is valid, the ResultSet will contain all that information. Creating a regex to parse a statement that already has a parser built into your database seems like re-inventing the wheel to me.

But if it's just a regex exercise for fun, forget what I said.

Winston

[Campbell Ritchie]

Welcome to the Ranch

[Harsha Smith]

Tell me if this works .

String query = "select NVL(SUM(EMP_SAL), 0), MAX(EMP_AGE), EMP_NAME, DEPT_NAME, " +
				"NVL(DEPT_ID, \"\") from EMP_TABLE ";
		
	
		String regex = "[A-Z]+_[A-Z]+";
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(query);
		
		while(matcher.find()){
			if(!matcher.group().contains("TABLE"))
			System.out.println(matcher.group());
		}

[Martin Vajsar]

Winston Gutkowski wrote:I guess my question is why?

I guess that someone wants to track Java code dependencies on database schema objects.

[avishek bose]

Thanks a lot for your great help...

[avishek bose]

hi Winston .. we require it as we want to validate the column names are proper before firing the query in database as these queries are taken from a xml file where the queries are modified by end users. If column names are proper then the query will be executed at the DB end. else it will redirect to error.jsp with a proper message.

[Martin Vajsar]

avishek bose wrote:hi Winston .. we require it as we want to validate the column names are proper before firing the query in database as these queries are taken from a xml file where the queries are modified by end users. If column names are proper then the query will be executed at the DB end. else it will redirect to error.jsp with a proper message.

Do you realize that you're exposed to the SQL injection attacks in the most serious way imaginable? Your end users might wreak havoc in the database even inadvertently; if there is ever a malicious user, you're going to have tough time.

[Rob Spoor]

Perhaps it's a better idea to use an SQL parser. Zql can parse SQL statements. From the little experience I have:

InputStream in = new ByteArrayInputStream(query.getBytes());
ZqlParser parser = new ZqlParser(in);
ZQuery query = (ZQuery)parser.readStatement();
Vector<?> select = query.getSelect();
for (Object o: select)
{
    ZSelectItem item = (ZSelectItem)o;
    ZExp exp = item.getExpression();
    if (exp instanceof ZConstant)
    {
        ZConstant constant = (ZConstant)exp;
        if (constant.getType().equals(ZConstant.COLUMNNAME))
        {
            String column = constant.getValue();
        }
    }
    else if (exp instanceof ZExpression)
    {
        ZExpression expression = (ZExpression)exp;
        Vector<?> operands = expression.getOperands();
        // operands again contains ZExp objects
    }
}

[Harsha Smith]

avishek bose wrote:Thanks a lot for your great help...

was that for me? if yes, you're welcome.