Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,008 Comments

Discussions

Replace multiple whitespaces in a string with single one

587500
587500 Member Posts: 2
edited July 2008 in SQL & PL/SQL
Hi,

I'm using Oracle 9.2.0 and I have a query on string manipulation.

Can I replace multiple whitespaces within a string with single one. I tried with regexp_replace function but it is not supporting Oracle 9i.

Can someone help me out?

Regards,
Pramod

Comments

  • 121256
    121256 Member Posts: 1,054
    edited July 2008
    Oracle 9.2.0
    Can I replace multiple whitespaces within a string with single one.
    Using some dummy character (e.g. chr(0)):
    select replace(replace(replace(
      'Can  I   replace      multiple  whitespaces   within    a     string    with single one',
      '  ', ' ~'), '~ '), '~') from dual;
    
    REPLACE(REPLACE(REPLACE('CANIREPLACEMULTIPLEWHITESPACESWITHINASTRI
    ------------------------------------------------------------------
    Can I replace multiple whitespaces within a string with single one
  • 587500
    587500 Member Posts: 2
    Many Thanks Elic.
  • venkat
    venkat Member Posts: 372
    Hi elic,
    wts mean by '~' symbol what it will does. pls give more Idea abt this.

    Regards,
    Venkat.
  • Sven W.
    Sven W. Member Posts: 10,511 Gold Crown
    edited July 2008
    Elic was using the
    ' ~'
    and
    '~ '
    strings (the blanks are important) as terminators for the start and end position of the whitespace area.

    Try to run each replace statement in some separate step and you will see how it works.

    Message was edited by:
    Sven W.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited July 2008
    select
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    'Can I replace multiple whitespaces within a string with single one'
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ') as str
    from dual;
    STR
    ------------------------------------------------------------------
    Can I replace multiple whitespaces within a string with single one
    If we use replace 6 times,
    About 64 continuous spaces will be compressed.
    because 2**6 = 64
  • 636403
    636403 Member Posts: 273
    edited July 2008
    easiest way:
    SQL> select replace(replace(replace('Can  I   replace      multiple  whitespaces   within    a     string    with single one',
    2 ' ',
    3 '123'),
    4 '231',
    5 ''),
    6 '123',
    7 ' ')
    8 from DUAL
    9 ;

    REPLACE(REPLACE(REPLACE('CANIR
    ------------------------------------------------------------------
    Can I replace multiple whitespaces within a string with single one

    SQL>
    :)

    Message was edited by:
    qube

    It seems that the method has the same idea as Elic method, but it's independent one :)

    Message was edited by:
    qube
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Your idea is great.
    case1
    AAAAAAAAAAAAAAAAAAAAAAAAA
    replace AA to AB
    ABABABABABABABABABABABABA
    remove BA
    A
    remove B
    A
    case2
    AAAAAAAAAAAAAAAAAAAAAAAA
    replace AA to AB
    ABABABABABABABABABABABAB
    remove BA
    AB
    remove B
    A
  • 121256
    121256 Member Posts: 1,054
    Your idea is great.
    It's not mine. I just [url http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=88473&pg=-1&hl=replace+%ef%f0%ee%e1%e5%eb%fb#643244]remember it.
  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited July 2008
    (deleted because I didn't read the whole thread)

    Message was edited by:
    dombrooks
  • 121256
    121256 Member Posts: 1,054
    edited July 2008
    It seems that the method has the same idea as Elic method, but it's independent one :)
    And somewhat erroneous. Try (rpad('You', 1400) || 'can`t') :)
  • 450441
    450441 Member Posts: 2,525
    Change the 123 to 12 and the 231 to 21 and it works on my 9.2.0.6 database.
  • 636403
    636403 Member Posts: 273
    edited July 2008
    Yes, it seems incorrect... but...

    what'is that?
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 

    SQL> select replace(replace(replace((rpad('You', 1335) || 'can`t') , ' ', '123'), '231', ''), '123', ' ') as str from dual;

    STR
    -----
    You c

    SQL>
    SQL> select substr(replace(rpad('You', 1335) || 'can`t', ' ','123'),-10) as str  from dual
    2 union all
    3 select substr(replace(rpad('You', 1334) || 'can`t', ' ','123'),-10) from dual
    4 union all
    5 select substr(replace(rpad('You', 1333) || 'can`t', ' ','123'),-10) from dual
    6 union all
    7 select substr(replace(rpad('You', 1332) || 'can`t', ' ','123'),-10) from dual
    8 ;

    STR
    ----------
    123123123c
    123123can`
    23123can`t
    23123can`t

    SQL>
  • 611118
    611118 Member Posts: 379
    Just playing around:
    WITH
    Strings
    AS
    (
    SELECT 'Can I replace multiple whitespaces within a string with single one' Text FROM Dual
    )
    SELECT
    REPLACE
    (
    REPLACE
    (
    XMLAGG
    (
    XMLElement
    (
    "A",
    SubStr(Text, Level, 1)
    )
    ),
    '</A>'
    ),
    '<A>'
    ) Text
    FROM
    Strings
    WHERE
    NOT
    (
    Level > 1
    AND SubStr(Text, Level, 1) = ' '
    AND SubStr(Text, Level - 1, 1) = ' '
    )
    CONNECT BY
    Level <= LENGTH(Text);
  • 636403
    636403 Member Posts: 273
    edited July 2008
    The answer is
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm#sthref923

    4000 bytes limit is exceeded.
    SQL> select 1333*length('123') from dual
    2 union all
    3 select 1334*length('123') from dual;

    1333*LENGTH('123')
    ------------------
    3999
    4002

    SQL
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Just playing around:
    Dito ;)
    SQL> var str varchar2(200)
    
    SQL> exec :str := 'Can  I   replace      multiple  whitespaces   within    a     string    with single one'
    PL/SQL procedure successfully completed.
    
    SQL> select xmltransform(xmlelement(e, :str),
                        xmltype('<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                                 <xsl:template match="/">
                                 <xsl:value-of select="normalize-space(E/text())"/>
                                 </xsl:template>
                                 </xsl:stylesheet>')) str from dual
    /
    STR                                                                   
    ----------------------------------------------------------------------
    Can I replace multiple whitespaces within a string with single one    
    1 row selected.
  • 611118
    611118 Member Posts: 379
    Kewl. :)

    Now, without XML at all, and no restrictions on characters, such as the '~' in the first, rather ingenious, reply.
    WITH
    Strings
    AS
    (
    SELECT 'Can I replace multiple whitespaces within a string with single one' Text FROM Dual
    )
    SELECT
    SUBSTR(MAX(SYS_CONNECT_BY_PATH(Trimmed, ' ')), 2) A
    FROM
    (
    SELECT
    L,
    RowNum R,
    TRIM
    (
    SubStr
    (
    Text,
    L,
    LEAD(L, 1, LENGTH(Text) + 2) OVER(ORDER BY L) - (L + 1)
    )
    ) Trimmed
    FROM
    (
    SELECT
    Level L,
    Text
    FROM
    Strings
    WHERE
    NOT
    (
    Level > 1
    AND SubStr(Text, Level, 1) = ' '
    AND SubStr(Text, Level - 1, 1) = ' '
    )
    CONNECT BY
    Level <= LENGTH(Text)
    )
    WHERE
    (L = 1 AND SubStr(Text, 2, 1) <> ' ')
    OR SubStr(Text, L - 1, 1) = ' '
    )
    START WITH
    L = 1
    CONNECT BY
    R = PRIOR R + 1;
  • 121256
    121256 Member Posts: 1,054
    Change the 123 to 12 and the 231 to 21 and it works
    :-))) Change 1400 to 2000 and it willl again not work.
  • 121256
    121256 Member Posts: 1,054
    and no restrictions
    Try to spread your solution on generic case (multiple lines rowsource) :)
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    my life is much better as i use 10g :-)

    select regexp_replace('Can I replace multiple whitespaces within a string with single one',' * ', ' ') from dual
  • 623666
    623666 Member Posts: 174
    I like below one.
    select
    regexp_replace('Can I replace multiple whitespaces within a string with single one',' {2,}',' ')
    from dual
  • 121256
    121256 Member Posts: 1,054
    my life is much better as i use 10g :-)
    Have you read original posting?
  • 611118
    611118 Member Posts: 379
    Try to spread your solution on generic case (multiple lines rowsource)
    There's always a problem.

    Any ideas?
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    There's always a problem.
    Any ideas?
    Too convoluted for me and again a restriction for strings smaller than 4k:
    SQL>  with t as
         (select 'Can  I   replace      multiple  whitespaces   within    a     string    with single one' s from dual union all
          select 'Do Can  I   replace      multiple  whitespaces   within    a     string    with single one' s from dual
    )
    --
    --
    select s, max(sys_connect_by_path(trim(s2),' ')) s2 
      from (select s,
                   s2,
                   row_number() over (partition by s order by l) rn 
              from (select s,l,     
                           substr(s, lag(instr(s,' ',1,l),1,0) over (order by 1) + 1, nvl(nullif(instr(s,' ',1,l),0),length(s)) - lag(instr(s,' ',1,l),1,0) over (order by 1) ) s2
                      from (select  s, level l
                             from t connect by level <= length (s) - length (replace (s, ' ')) + 1
                              and prior s = s and prior sys_guid () is not null)
                    )
             where trim(s2) is not null)
    connect by prior s = s and prior rn = rn -1 start with rn = 1
      group by s
    
    S                                                                                            S2                                                                         
    -------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
    Can  I   replace      multiple  whitespaces   within    a     string    with single one       Can I replace multiple whitespaces within a string with single one        
    Do Can  I   replace      multiple  whitespaces   within    a     string    with single one    Do Can I replace multiple whitespaces within a string with single one     
    
    2 rows selected.
  • 611118
    611118 Member Posts: 379
    Mine was convoluted? :)

    Nice answer though.
This discussion has been closed.