Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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.orgReceived on Mon Jul 10 2006 - 17:05:31 CDT
![]() |
![]() |