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 .
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?
Joined: Dec 22, 2004
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
2. unescape the wrongfully escaped ones
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.
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:
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.
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.
The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus