Need help on sys_connect_by_path

Hi,
I am using sys_connect_by_path function.
the field from which information is fetched contains special character which sys_connect_by_path doesnt support.
and because of which sql is erroring out,
Could you please help as to how to remove the special character for sys_connect_by_path funct,,
Below is the sql m using.. Please help
select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         (FIELD NAME), ' > ')), ' > ') path
  from TABLE NAME
where CONDITION
start with
CONDITION
connect by prior
     CONDITION

TechygalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of oracle are you using? you posted in Oracle 8 zone, for which version that function is NOT available...
0
TechygalAuthor Commented:
Oracle 8i..thats Release 8.1.7.0.0 ..
does this helps..
Function is working fine.. bt it is used only for varchar.. char.. n all of those.. bt if the field contains special character .. its erroring out...

so wat can I use to remove the special character when its been selected..
 
I have pasted the SQL on my question..
Kindly hav a look at it
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>the field from which information is fetched contains special character which sys_connect_by_path doesnt support.

which are those characters?

you could "translate" those characters, for example if "%&/()=" are characters not working, translate them "away":
select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         ( TRANSLATE(FIELD NAME, '%&/()=' ,' ' )), ' > ')), ' > ') path
  from TABLE NAME
where CONDITION
start with
CONDITION
connect by prior
     CONDITION

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TechygalAuthor Commented:
Thank U so MUCH..... i did try translate. bt wat went wrong was the replacing value,,  i used '' rather ' '( space was missin)... :)..

you are really great.. appreciate your help..


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help. and yes, the ' ' instead of '' in oracle is important, as '' is implicitly considered NULL, and then TRANSLATE does not do anything.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TechygalAuthor Commented:
Good Job... :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.