While I suspect there probably is still a way of doing it with regex, you can do it with simple string parsing as well.
Find all commas in the string, then go forwards or backwards from that point (it won't matter which if the brackets are balanced correctly), adding one for an open brace and subtracting one for a closed brace. If you don't have 0 at the end, you're inside a brace, so it's not a comma you want.
Split on all other commas.
EDIT
Although the comment about this methodology failing in the case of parentheses enclosed in commas is valid, it may be the case that you're dealing with queries simple enough not to worry about that. If that is the case, this should work:
def in_brackets(str,pos)
cnt = 0
str[pos,str.length].each_char do |c|
if c == '('
cnt += 1
elsif c == ')'
cnt -= 1
end
end
return cnt != 0
end
def split_on_some_commas(str)
offset = -1
split_pts = []
while (offset = str.index(",",offset+1))
if !in_brackets(str,offset)
split_pts << offset
end
end
split_pts << str.length
pos = 0
ret = []
split_pts.each do |pt|
ret << str[pos..(pt-1)].strip
pos = pt+1
end
return ret
end
puts split_on_some_commas("SUBSTRING('test',1,3) ASC, SUBSTRING('test2', 2,2 ) DESC, SUBSTRING('test2', 2,2 ) DESC").inspect