Re: Dumb SQL Question

Thanks Daniel!
I know, it is a pretty boneheaded design, which is why we were trying to clean it up a bit.
Fortunately there aren't too many of these, but it worries me in the future.
We're using ClearCase, Harvest, HP Openview, and they are all used differently of course and the fields are free form a lot of times, so getting all of these change requests to sync up is a real pain! So we thought exporting the data from all of these sources and importing into access for our own tracking would be a good interim solution, but it appears that this is a big potential disaster. - Not to mention the LONG fields in oracle, that we can't extract easily.

Anyway, we can edit these fields manually, but I didn't know if there was a way to Replace looking for a comma in a specific position, like position 3 only.

DA Morgan wrote:
> 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
Received on Mon Jul 10 2006 - 15:26:51 CDT