regex - Limit the Number of Characters between Double Quotes using Oracle 11g PL/SQL -

i need way of applying substring limit of 32 characters each grouping of data inside double quotes, using oracle 11g pl/sql.

basically using following example string provided me is, not have control of way string constructed:

str := ‘"aaaaaaa bbbbbbb ccccccc dddddd1","aaaaaaa bbbbbbb ccccccc dddd",”qwerty”,”n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1”’; 

i need go through each group of values between each double quote, example (“qwerty”) , apply substring of 32 every grouping found.

so using above string example, it’s need perform following:

str := ‘"substr(‘aaaaaaa bbbbbbb ccccccc dddddd1’,1,32)","substr(‘aaaaaaa bbbbbbb ccccccc dddd’,1,32)",”substr(‘qwerty’,1,32)”,”substr(‘n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1’,1,32)”’; 

so in end, end result str consist of 4 groupings each grouping's length less or equal 32.

any great.

thanks.

you can make use of regexp_replace.

regexp_replace(str,'"([^"]{1,32})[^"]*"','"\1"') 

explanation of pattern:

"               --matches double quote ([^"]{1,32})    --matches group of 1 32 characters, containing other double quotes.                 --surrounded brackets, form first capture group. [^"]*           --matches 0 or more characters other double quotes "               --matches double quote 

sql fiddle

with x(str) ( select '"aaaaaaa bbbbbbb ccccccc dddddd1","aaaaaaa bbbbbbb ccccccc dddd","qwerty","non1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1"' str dual ) select str, regexp_replace(str,'"([^"]{1,10})[^"]*"','"\1"') x 

results:

|                                                                                                              str | regexp_replace(str,'"([^"]{1,10})[^"]*"','"\1"') | |------------------------------------------------------------------------------------------------------------------|--------------------------------------------------| | "aaaaaaa bbbbbbb ccccccc dddddd1","aaaaaaa bbbbbbb ccccccc dddd","qwerty","non1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1n1" |  "aaaaaaa bb","aaaaaaa bb","qwerty","non1n1n1n1" |