Re: Dumb SQL Question

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
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

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 www.psoug.org

-- 
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