Re: Replacing Strings

On Sep 11, 3:42 pm, sybra..._at_hccnet.nl wrote:
> On Thu, 11 Sep 2008 12:01:25 -0700 (PDT), e..._at_chicagorsvp.com wrote:
> >On Sep 11, 1:41 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> >> <e..._at_chicagorsvp.com> a écrit dans le message de news: adee164f-6729-416f-8eca-35556d30f..._at_m45g2000hsb.googlegroups.com...
> >> | Hi,
> >> |
> >> | We have some messages stored in a column in this format:  AA|BB|CC|DD
> >> |
> >> | What we want is to replace the vertical bars with a CHR(10) for line
> >> | feed, but have 3 leading spaces.
> >> |
> >> | Everything I try seems to only indent the first item 3 characters.
> >> | Even I try to replace it with '   ' || CHR(10) and it ignores the
> >> | spaces.......
> >> |
> >> | What we want is:
> >> |   AA
> >> |   BB
> >> |   CC
> >> |
> >> | Ideas anyone?
>
> >> replace(column,'|',chr(10))
>
> >> Regards
> >> Michel
>
> >But what about the leading spaces?  Those disappear if I use replace
> >or translate or even LPAD........
>
> I suggest you check (and post the results and your exact code) by
> prepending the string with an '*' or something like that.
> You must be doing something wrong: either your code is wrong, or you
> have a display problem.
> Also you should also, in *every post* include your complete version
> number.
> We can not be bothered to memorize it.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Sorry Sybrand.

I wound up writing some loop to parse on the delimiter......only 5 lines, but I could not find a way to take a string like this:

AA|BB|CC

and put it into this: Notice the leading spaces:

   AA
   BB
   CC

So, I wrote the loop thingy. I used this to find the number of delimiters:

SELECT LENGTH(v_rec.messages) -
LENGTH(REPLACE(v_rec.messages,'|',NULL)) INTO v_num_messages FROM dual;

Sort of cryptic, but I think I understand it. Taking the length of the string, and subtracting the length of the string not including the delimiters. Received on Fri Sep 12 2008 - 13:40:22 CDT