Re: Dumb SQL Question

Holly wrote:
> 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!

There is often no fix for stupidity (bad design) other than one record at a time and going it manually. But if you've got 10g I'd suggest looking at Regular Expressions.

My instinct here would be to first move all "good" records and then break down what is left by category, perhaps fixing each category separately.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org