Regex for incomplete lines within known start and end strings

2
votes

I want to insert the following into a database:

(#text1#,#text2#,#text3#,#text4#,#text5#,#text6#, #text7#, #text8#, #text9#), (#text1#,#text2#,#text3#,#text4#,#text5#,#text6#, #text7#, #text8#, #text9#), (#text1#,#text2#,#text3#,#text4#,#text5#,#text6#, #text7#, #text8#, #text9#);

but sometimes I will not have nine textfields that I can place into my database; e.g.

(#text1#,#text2#,#text3#,#text4#,#text5#,#text6#, #text7#, #text8#, #text9#), (#text1#,#text2#,#text3#,#text4#,#), <<<--- String breaks and messes up my insert (#text1#,#text2#,#text3#,#text4#,#text5#,#text6#, #text7#, #text8#, #text9#);

What regex will delete lines with fields that don't have both start and end tags? Edit: The lines themselves will always have the start tag (# and the closing tag #).

I tried /^\(#.*?#,#.*?#,#.*?#,#.*?#,#.*?#,#.*?#,#.*?#,#.*?#,#.*?#\)$/ig but it didn't work.

I created a page where you can insert a regex to see if your solution works.

4
databaseregex
htstring29.replace(/^((#.*?# ){0,8}),$/g, ''); I have tried this and it does not seem to work either? – Gerald Ferreira
It seems to me that the columns in your DB might want to be rows instead (I'd normalize the table, if you have that option) – onupdatecascade
Oh how you tempt us with the "expert should solve this in seconds" :) – Lucas Jones
I am surprised Person b :-) I thought it is like so easy to do :-) – Gerald Ferreira
+1 for the effort you put in your question : online-affiliate-programs.co.za/test/index.asp – Andreas Grech

4 Answers

2
votes

How about:

/^\((?:#.+#,\s*){8}(?:#.+#\s*)\)[,;]$/gm

That is 8 non-capturing groups of a text field with one or more characters followed by a comma and optional whitespace, and one more text field with no comma all inside literal parentheses, and followed by a comma or semicolon. If you have multiple lines in one text string, make sure to use the "/m" switch so that "^" and "$" match newlines.

You should be able to use this to extract all the valid lines. Deleting other lines is going to be harder...

update:

Got it. Here's one that matches lines with 8 or fewer pairs of "#" characters, or with an odd number of them:

^\((?:[^#\n]*?#[^#\n]*?#[,\s]?){0,8}(?:[^#]*#[^#]*)?\)[,;]\s*$

which would match a line like:

(#text1#,#text2#,#text3#,#text4#),

or a line like:

(#text1#,#text2#,#text3#,#text4#,#),

edit: the comma needs to be optional...

image

It looks like your new examples no longer are one-per-line, and you no longer have the "single #" case, so it can be simplified to:

\((?:[^#\n]*?#[^#\n]*?#[,\s]?){0,8}\)[,;]\s*
1
votes

You could try it with this:

/^\(([\s]*#[^#]+#,?){9}\)[,;]$/

edit:

In perl, if you want to remove occurrences of any pattern of your above set that has less than 9 #\d#'s, you can use the following:

$string =~ s/\(([\s]*#[^#]+#[\s]*,?){0,8}\)[,;]*//g;

It allows for spaces at either end of the #\d#, an optional comma separating them within the parens, and either a comma or a semi after the group. Your resulting $string will be the list of 9-token groups from your input string, as they appear in the original.

0
votes
/^\((#\w+#,?\s?){9}\)$/ matches exactly 9.
0
votes

Deleting from the string has some complications, such as what to do with the error line if it is last - contains the ;

line = ^\s*\(.*\)[,;]\s*$
a string token = #[\w\s]*#
a list of tokens = token(?:\s*,\s*token)
7 or less items {0,7}
a list of 8 or less tokens = token(?:\s*,\s*token){0,7}

Making

^\s*\(#[\w\s]*#(?:\s*,\s*#[\w\s]*#){0,7}\)\s*[,;]\s*$

with which you want to replace these lines with nothing globally treating the string as multiple lines /match/replace/gm

/^\s*\(#[\w\s]*#(?:\s*,\s*#[\w\s]*#){0,7}\)\s*[,;]\s*$//gm

If you have set your string character to # for the purpose of the insert then the token could be simplified to #[^#]+#

In your example short line the last token only has the one # which I have not allowed for here so far, nor an entirely empty element which might be acceptable to your sql parser.