Dumb SQL Question

Hi guys. I need some help with a Toad SQL query I'm doing. We have some data in ClearQuest that I'm trying to extract into a delimited file (with '&')

My query works except there is a tricky column (a free-form one) where the users can enter
values like 12345 or 12,345 or 12345,99999,121212. And sometimes they enter AB12345.
This is a big pain, because I'm trying to split the 12345,99999,121212 into separate columns 1, 2 and 3. But then the 12,345 gets me because it is then delimited into two separate columns 1 and 2 when it should have been 12345 in the first place but the user entered it wrong and ClearQuest doesn't check the formatting.

I was using a replace to get the commas out, like:

replace(replace(replace(t1.dumb_cm,'AB'),'&','?'),',','&')||'?'||

and also create '&' as my delimiter.

But now the 12,345 is becoming 12&345& and I don't know how to remove/replace/instr/expreg the thousands separator column out?

I hope this made sense, and if anybody can help out that would be great! Received on Mon Jul 10 2006 - 13:09:25 CDT