Regular Expression Help Needed

[Ganesh Gowtham]

String str = "L''ORE'ALX";
str.replace("'","''"); will give ooutput like L''''ORE''ALX which i dont need .

select * from Table where column like 'L''ORE'ALX%' / it will throw bad sql grammars exception since we have single quote ( ' ) needs to relapced to double quote( '') select * from Table where column like 'L''ORE''ALX%'

so i need only convert the ( ' ) to ( '') which is in between E and A ,so that final string will be
'L''ORE''ALX'

so in regular exp i need to search with pattern like single quotes between any charactres so that i can replace to ( '') quotes .

pls do help .

[Ganesh Gowtham]

Hi Folks

I came up with some code which is apt for above example but needs refactoring

  String str = "L''ORE'ALX'OOPS''END";
String[] x = Pattern.compile("'").split(str);
 
for (int i = 0; i < x.length; i++) {
if (i != x.length - 1) {
if (x[i].trim().length() != 0) {
System.out.print(x[i]);
System.out.print("''");
}
 
} else {
System.out.print(x[i]);
}
 
}  

Please comment or suggest me

[Guido Sautter]

Before suggesting you how you can do the replacement, could you tell what the result of the replacement action should look like? Seems to have something to do with escaping Strings for SQL statements, but what String do you actually want to write to the database, i.e. what should the String look like if you read it back from the database?

[Guido Sautter]

Thanks for the explanation, though sending it as a private message is not the way to go ... it will make it harder for later readers to follow this thread.

As for your question, I'd try it in two steps:
1. escape all the ' characters

myQueryString = myQueryString.replaceAll("'", "''");

2. unescape the wrongfully escaped ones

myQueryString = myQueryString.replaceAll("''''", "''");

The second replace operation will undo the first where necessary. I'm pretty sure this can be done in one step with a more sophisticated regular expression, but this way is probably the easiest and also quickest, since matching will be pretty fast with such simple patterns.

[Jim Yingst]

If this is being done for SQL, it may be a good idea to use a PreparedStatement instead. Then you don't have to worry about escapes at all - you just call setString(), and escapes are all handled by the driver.

The original input string L''ORE'ALX doesnt' make much sense to me. Is this a case where someone already escaped the first ' correctly, but failed to escape the last one? So we're just looking for things that didn't already get escaped correctly?

Guido's two-step approach seems to make sense. Another option is to replace ' only if there is no other ' before or after:

str = str.replace("(?<!')'(?!')", "''");

Here (?<!') means from this position, you cannot look backwards and see a ', while (?!') means from this position you cannot look forwards and see a '. In between is the single ' that you do want to see. This should work, though Guido's approach may well be easier to read.

[Ganesh Gowtham]

Hi Jim and Guido

Thanks for your reply pls see my code

  String inputString = "L''ORE'ALX'OOPS''END";
System.out.println(inputString.replace("(?<!')'(?!')", "''"));  
 
above code emits L''ORE'ALX'OOPS''END  but i need is [B]L''ORE''ALX''OOPS''[/B]
 

please help me

[Ganesh Gowtham]

Hi JIm and Gildo

All i need is the regular expression which finds the pattern char/digit'char/digit ( example a'9 or 9'9 so on )
and then i need to replace the pattern with if single quote then double quote

please help

[Jim Yingst]

Oops, I should have said replaceAll() in my post, rather than replace().

[Ganesh Gowtham]

Hi Jim,

Thanks a lot for your kind help .

i did not notice that i have one method in String which takes regular expression as input .

[Ilja Preuss]

I would very strongly second the suggestion to use a PreparedStatement. It saves you a lot of trouble dealing with special characters *and* is likely to lead to slightly better performance *and* typically makes the code easier to read and maintain, in my experience.