The Cross Join is there to create multiple records, but no more than the longest intended individual words (three in this case).īecause each ID would result in three records, which is alright for ID 1, it will also create three records for ID 5 and ID 8. The trick here is in lines 11 through 17. The explanation for the regular expression can be found in the original post.ģ select 1 id, 'joey,anthony,marvin' str from dual union allĤ select 5 id, 'tony,glenn' str from dual union allĩ, regexp_substr (str, '+', 1, rn) splitġ3 from (select max (length (regexp_replace (str, '+'))) + 1 mxġ8 where regexp_substr (str, '+', 1, rn) is not null
Just a little while ago someone asked how to split it when you have more records involved than just one (as I used in my example).įor this example I use the dataset as AnthonyJ used in the comments. Over two years ago I wrote about a way to split a comma delimited string using Regular Expresssions.
0 kommentar(er)
