Java RegEx not replacing all expected patterns

Question

All I am trying to achieve is to replace comparative equals with ILIKE, but not assignment statements.

i.e.

SELECT * FROM db WHERE SUBJECT_TYPE = 'Person';

should be replaced as

SELECT * FROM db WHERE SUBJECT_TYPE ILIKE 'Person';

However

SET session ABC.VAR = 5;

should remain untouched.

For this I am trying to use lookahead such that there is no SET before equals (=).

Java

String str = "SELECT * FROM db WHERE SUBJECT_TYPE = 'Person' AND PREDICATE = 'name' AND OBJECT_VALUE = 'John' AND VERSION = '0' "
final Pattern EQUALS_ILIKE_PATTERN = Pattern.compile("(?i)((^((?!SET)[^=])+?)\\s*)=");

final Matcher matcher = EQUALS_ILIKE_PATTERN .matcher(str);

while (matcher.find())
{
    final String fromString = matcher.group(0);

    str = matcher.replaceAll("$1 ILIKE");
}

System.out.println(str);

Actual outcome:

SELECT * FROM db WHERE SUBJECT_TYPE ILIKE 'Person' AND PREDICATE = 'name' AND OBJECT_VALUE = 'John' AND VERSION = '0'

Expected outcome:

SELECT * FROM db WHERE SUBJECT_TYPE ILIKE 'Person' AND PREDICATE ILIKE 'name' AND OBJECT_VALUE ILIKE 'John' AND VERSION ILIKE '0'

The actual outcome here doesn't replace all necessary equals (=).

Tim Biegeleisen · Accepted Answer · 2019-06-25 04:43:17Z

1

We can do this with a one-liner call to String#replaceAll:

 String str = "SELECT * FROM db WHERE SUBJECT_TYPE = 'Person' AND PREDICATE = 'name' AND OBJECT_VALUE = 'John' AND VERSION = '0' ";
str = str.replaceAll("(WHERE|AND)\\s+(\\S+)\\s+=(?=\\s*'[^'+]+')", "$1 $2 ILIKE");
System.out.println(str);

This outputs (formatted for readability):

SELECT *
FROM db
WHERE
    SUBJECT_TYPE ILIKE 'Person' AND
    PREDICATE ILIKE 'name' AND
    OBJECT_VALUE ILIKE 'John' AND
    VERSION ILIKE '0'

edited Jun 25, 2019 at 4:43

answered Jun 25, 2019 at 4:37

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

6 Comments

Pratik Over a year ago

This regex won't take care of assignment thing i.e. the one which contains 'SET'.

Tim Biegeleisen Over a year ago

Updated to target only the WHERE clause. Now the SET clause won't be updated.

Pratik Over a year ago

I am not really concerned about WHERE|AND , Just want to negate SET. Also I am wondering why my regex is not working for this ?

Tim Biegeleisen Over a year ago

(?!SET) is a zero width negative lookahead. You never actually match any content after the lookahead, so it doesn't make much sense. If you want a more robust solution, you would have to write a SQL parser, so I won't update my answer again.

Pratik Over a year ago

would you like to update the answer with working solution at least ?

|

Collectives™ on Stack Overflow

Java RegEx not replacing all expected patterns

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related