Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Dumb SQL Question

Re: Dumb SQL Question

From: DA Morgan <>
Date: Mon, 10 Jul 2006 15:05:31 -0700
Message-ID: <>

Holly wrote:
> 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
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group

Please don't top post.

As I mentioned, if you have 10g there is. Look up Regular Expressions. I have demos in Morgan's Library at

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Mon Jul 10 2006 - 17:05:31 CDT

Original text of this message