MySQL Regexp to repeat CSV limits at 340 commas

Question

I am trying to create regexp to validate comma separated name value pairs which starts from ID like so

select
'ID,2190,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def,abc,def'

regexp 
'^ID,[1-9][0-9]*(,[^,]+,[^,]*)*$'

the above query will output 0 istead of 1, but if i reduce a pair ,abc,def this will reduce number of commas and the query will output 1

I have tried changing server, on a shared server the query runs fine irrespective of number of values

Can anyone tell me, is my query failing because of server configuration or something wrong in my regexp pattern, here i am trying to repeat a capture group 0 to infinite times

Answer 1

The solution was to use non-capturing group (?:) instead of capturing group ()

'^ID,[1-9][0-9]*(?:,[^,]+,[^,]*)*$'

the comment from Andy was useful with regex101 link where in description it states you should use non-capturing group when the data is not important

A repeated capturing group will only capture the last iteration. Put a capturing group around the repeated group to capture all iterations or use a non-capturing group instead if you're not interested in the data

Answer 2

WHERE col REGEXP '^ID,[1-9][0-9]*'
  AND ( LENGTH(col) -
        LENGTH(REPLACE(col, ',', '')) ) % 2 = 0

The second part checks whether there are an even number of commas in col. (Change 0 to 1 if you need an odd number.)

This formulation probably achieves your goal faster and avoids any regexp size limitation.