PL/SQL Programming eBook for free - and legal!

Toad for Oracle
41

Oops – there’s a bug. That formula won’t work if there’s only one
non-comma character in the whole string. But this one will work:

regexp_substr(regexp_substr( ‘,’ || i . i || ‘,’ , ‘([^,].*[^,]|[^,])’ ),
‘[^,]+$’ )

Nate Schroeder

Enterprise Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592

42

Thanks Nate. Excellent solution. That works.

43

I am sorry if I wasn't very clear and precise.

Ahh… I think I get you now. Let’s use slightly different data in order to be
absolutely clear:

44

Thanks Roger for your wonderful inisght and leading me towards the correct
solution. It had got me thinking in the right direction until Nate served it out
to me in a golden platter.

Thanks all for the wonderful help. Much appreciate it.

45

Nate served it out to me in a golden platter

Well… that depends :slight_smile:

It might only be a silver platter depending on what happens next. If a subtle
bug shows up, will you be able to debug and solve it?

Sometimes it’s better to go with a not-quite-so-elegant solution in order to
resolve any issues later… or even make changes to it.

I am totally green on regular expressions so I don’t know if Nate’s solution
is perfect or if it’ll choke on some special character in a different data
format – like French characters.

How about if a change is required and the business rules want a second character
ignored in the selection?

Ultimately, it’s up to you to test the solution against the data you’re
providing the solution for.

A developer should always know what will happen to his code when any value is
passed in to it. Either the value is expected and handled properly, or the value
is invalid and should be trapped in an appropriate exception mechanism.

Roger S.

46

Hmmm, What about unit testing, which allows re-run when subtle changes are
required.

Best Regards

Timothy Bryan

We are what we repeatedly do. Excellence, therefore, is not an act but a habit.

Aristotle

47

What about unit testing

Exactly! While Vikram was quite happy with Nate’s solution, it’s still his
responsibility to ensure the solution works for his environment :slight_smile:

Roger S.