Trouble with regular expressions

[Tiago Fernandez]

Hi all,

I'm having problems handling regular expressions in Java. I don't have experience with that and I'm reaching a deadline. The problem is I'm using a tool to generate INSERT scripts, but it's broken for some reason, and I have to fix the generated .sql file on my side. Simply, I have to replace all '[' for '', all ']' for '' and finally all 'N'' for '''.

INSERT INTO [SOME_TABLE] ([SOURCE_ID], [SOURCE_NAME], [PRIORITY], [CYCLE], [TSTAMP])
VALUES (1, N'EAD', 1, NULL, NULL)

should be:

INSERT INTO SOME_TABLE (SOURCE_ID, SOURCE_NAME, PRIORITY, CYCLE, TSTAMP)
VALUES (1, 'EAD', 1, NULL, NULL)

I know I could use String.replace() method for '[' and ']', but I need an regular expression for "N'" using String.replaceAll(). I already tried "[N][']" and "N'", but doesn't work. Can anybody help me?

I'd appreciate... thanks a lot!

[Jim Yingst]

I think we need more info. I see that you want to drop the N in

N'EAD'

but apparently you don't want to drop it in

NULL

So I think we may need some more examples of cases where N appears which you want to drop.

As a guess though, if the cases where you want to drop N are always of the form

N'XXX'

then you might use

str.replaceAll("N('\\w*+')", "\\1");

where \\1 is a reference to what was found in group 1, which is whatever was inside the first (and only) set of parentheses in the replacement pattern. In other words, it gives you whatever was represented by '\\w*+' in the regex.

[Tiago Fernandez]

Thanks for replying Jim, and yes, I just wanna get rid of "N" while finding something like N'EAD'.

Well, I tried what you sugested, but it didn't work yet. I realised it works when I do something like:

String test = "VALUES (1, N'EAD', 1, NULL, NULL)";
test = test.replaceAll("N'", "'");

But the same code doesn't work when I read the text from a file, something like:

byte[] rawData = IoUtil.readFileContents(file);
String fileContents = new String(rawData);
fileContents = fileContents.replaceAll("N('\\w*+')", "\\1");

File updated = new File(file.getPath() + ".DDL");
FileWriter output = new FileWriter(updated);

try {
output.write(fileContents);
output.flush();
} finally {
output.close();
}

Can you guess what's wrong?

Thanks in advance.

[Jim Yingst]

Sorry, I should have said

test = test.replaceAll("N('\\w*+')", "$1");

Was thinking of something else there.

[Kevin Davies]

 "N('\\w+')" 

, you cannot have a quantifier '+' of a quantifier '*'. Also you could make it more efficient by not doing a capture but doing a "look ahead":

test = test.replaceAll("N(?='\\w*+')", "");  

[ July 08, 2005: Message edited by: Kevin Davies ]
[ July 08, 2005: Message edited by: Kevin Davies ]

[Alan Moore]

And, since the replacement is now the empty string, you can get rid of the square brackets at the same time:

test = test.replaceAll("\\[|\\]|N(?='\\w+)", "");

BTW, "\\w*+" is a valid regex. The plus sign makes the asterisk possessive; i.e., it will match zero or more word characters and never back off, even if that makes the overall match fail. In this case, it doesn't change what gets matched, but it makes the regex (negligibly) more efficient. Possessive quantifiers are mainly useful for making sure that matches that are going to fail, do so as quickly as possible. The JDK regex implementation was the first to implement them, but others are starting to follow suit.
[ July 09, 2005: Message edited by: Alan Moore ]