how to built regular expression to get value between two single quotes and if there is no single qoute, extract between commas

Question

Problem that i face:

-I have an input string, a SQL statement that i need to parse

-extract the value that need to be insert base on the column name specify

-i can extract the value that is wrap in between 2 single quotes, but:

--?what about value that has no single quotes wrap at them? (like: integer or double)

--?what if the value inside already has single quotes? (like: 'James''s dictionary')

Below is the sample input string:

INSERT INTO LJS1_DX (base, doc, key1, key2, no, sq, eq, ln, en, date, line) 
VALUES ('GET','','#000210','','   0','   1','5',1,0,'20100706','Street''James''s dictionary')

The Java Code i have below match value between two single quotes only:

 Pattern p = Pattern.compile("'.*?'");
 columnValues = "'GET0','','#000210','','   0','   1','5',1,0,'20100706','Street''James''s dictionary'";
 Matcher m = p.matcher(columnValues); // get a matcher object
 StringBuffer output = new StringBuffer();
 while (m.find()) {
  logger.trace(m.group());
 }

Appreciate if anyone can provide any guideline or sample to this question.

Thank you!!

Answer 1

Regex are not really suitable for this. You will always find cases that fail

A csv parser such as opencsv is probably a better option

Answer 2

I agree with gnibbler that this is a job for a csv parser.

A regex that works on your example would be

'(?:''|[^'])*'|[^',]+

which looks challenging to debug and maintain, doesn't it?

Explanation:

'            # First alternative: match an "opening" '
 (?:         # followed by either...
  ''         # two ' in a row (escaped ')
 |           # or...
  [^']       # any character that is not a '
 )*          # zero or more times,
'            # then match a "closing" '
|            # or (second alternative):
[^',\s]+     # match any run of characters except ', comma or whitespace

It also works if there is whitespace around the values/commas (and will leave that out of the match).

Answer 3

In general, when you need to parse complex langauges, regexps are not the best tool - there's too much context to make sense of. So, if reading XML use an XML parser, if reading C code, use a C language parser and if reading SQL ...

There's a Java SQL parser here, I would use somethink like this.

For other languages it may be best to use a "YACC"-like parser. For example JACK

Answer 4

instead you can get all values using subString after Values keyword. Same way we can get names also. then you will have two comma-separated string which can be converted to array and you will have a arrays for names and values. you can then check which param has which value .

hope this helps.

Answer 5

p = Pattern.compile("('?)(.*?)\\1(?:,|$)"); 
m = p.matcher(columnValues); // get a matcher object 
 while (m.find()) { 
  System.out.println(m.group(2)); 
 }

1. ('?) - Group 1 - Optional quote
2. (.*?) - Group 2 - Chars within quotes
3. \1 - To match first quote captured in group 1
4. (?:,|$) - To match comma or end of string - (?: - to ignore capturing of groups)

Answer 6

Regular expressions are not easy to use with this (but everything is possible).

I would suggest parsing it yourself, or use a library to do the parsing. By writing the parser yourself you are certain that it works exactly as you need it to.

Answer 7

I think Tim had the right idea; it just needs to be implemented more efficiently. Here's a much more efficient version:

'[^']*+(?:''[^']*+)*+'|[^',\s]++

It uses Friedl's "unrolled loop" technique to avoid excessive reliance on alternations that match one or two characters at a time (I think that's what did you in, Tim), plus possessive quantifiers throughout.