asked on

Regular Expression

table name = 'dbo.EXCHANGE', constraint name blah blah DELETE FROM dbo.EXCHANGE WHERE exchangeCode blah blah table name = 'dbo.EXCHANGE', constraint name = blah blah

I am trying to create a regular expression which will only find the word EXCHANGE once in the above string,
specifically in the '...DELETE FROM dbo.EXCHANGE WHERE...'

The regular expression I am trying to create is:

String regex = "[dbo]{1}.([A-Z]+?).[WHERE]{1}";
Pattern pattern = Pattern.compile(regex, Pattern.DOTALL);

However when I pring out what is found by the matcher, I get:
EX
EX
EX

while (matcher.find()) {

System.out.print(matcher.start(1) + "-" + matcher.end(1) + ": ");
System.out.println(matcher.group(1));
}

Can someone tell me the regex I need to use. I need to include the flag 'Pattern.DOTALL' when compiling the pattern.
There can also be any number of underscores (including none) in the table name, for example:

EXCHANGE
EXCHANGE_HISTORY
EXCHANGE_HISTORY_TEMP

etc

Thank you

ASKER CERTIFIED SOLUTION

Ajay-Singh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ

>>which will only find the word EXCHANGE once in the above string

What is your intention here?

SnO2d

ASKER

I'm running some DELETE sql but it is failing because of a constraint.
I therefore want to try to report something sensible back to the screen, not the stacktrace.

That works a treat, thanks

SnO2d

ASKER

What about underscores in the table name?

For example,
EXCHANGE
EXCHANGE_HISTORY
EXCHANGE_HISTORY_TEMP

SnO2d

ASKER

Got it ...

String regex = "[dbo]{1}.([A-Z]+?[_?[A-Z]*?]*?)\\W+[WHERE]{1}";

CEHJ

Isn't using regexes a bit over the top for such simple substringing functionality? You could use something like

      public static String truncateStatement(String statement, String tableName) {
            String sl = statement.toLowerCase();
            int i = 0;
            if ((i = sl.indexOf(tableName, i)) > -1) {
                  return (i = sl.indexOf(tableName, i + 1)) > -1? statement.substring(0, i).trim() : statement;
            }
            else {
                  return statement;
            }
      }

SnO2d

ASKER

I don't know the table name in advance. I only know it will appear between The strings 'dbo.' and ' WHERE'

CEHJ

oh ok