1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please Help.......Separating File Names with Spaces command

Discussion in 'SQL PL/SQL' started by Tomichi, Jan 11, 2011.

  1. Tomichi

    Tomichi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Dear master,

    please help me,
    I want to split a file name using the spaces,

    examples TommyGunawanSkom name (no spaces),
    so that looks to be Tommy Gunawan Skom(separated by spaces)...:confused::(
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Use this query

    Code (SQL):
    WITH C AS
     (SELECT 'TommyGunawanSkom' STR FROM DUAL)
     SELECT XMLAGG(XMLELEMENT(E,' '||STR1).EXTRACT('//text()'))
     FROM
     (SELECT REGEXP_SUBSTR(STR,'[A-Z][a-z]+',1,level) STR1
     FROM C
     CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(STR,'[A-Z]') FROM C)
     )

    XMLAGG(XMLELEMENT(E,''||STR1).EXTRACT('//TEXT()'))
    -----------------------------------------------------------
    Tommy Gunawan Skom
     
    Tomichi likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or like this.. .


    Code (SQL):

    SQL> SELECT TRIM( REGEXP_REPLACE('TommyGunawanSkom', '([[:upper:]]+)', ' \1'))   FROM dual;

    TRIM(REGEXP_REPLAC
    ------------------
    Tommy Gunawan Skom

    SQL>
     
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or ...

    Code (SQL):

    SQL> SELECT TRIM( REGEXP_REPLACE('TommyGunawanSkom', '([[:upper:]])([[:lower:]]+)', ' \1\2'))   FROM dual;

    TRIM(REGEXP_REPLAC
    ------------------
    Tommy Gunawan Skom

    SQL>
     
     
  5. Tomichi

    Tomichi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Code it's not work 'ORA-00904: "REGEXP_COUNT" :invalid identifier
     
  6. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    REGEXP_COUNT was introduced in Oracle 11g. Your database version would be a prior release 9i or 10g. It would be better if you try the solutions given by Raj