DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: sql statement to delete control character

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    sql statement to delete control character

    Hi

    could you tell me sql statement to delete control characters like chr(10) chr(13) from a particular column . i don't want this to be replaced by space .

    example

    list
    -----
    abcd ..... chr(10) hidden character present
    efgh

    this is single record it should display abcdefgh

    appreciate ur ideas.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Look at the translate function in the manuals.

    something like select (chr(10),chr(10),'XYZ') from dual;

    regards
    Hrishy

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    SQL> select 'a'||chr(10)||'b' from dual;

    'A'
    ---
    a
    b


    SQL> select replace('a'||chr(10)||'b',chr(10),'') from dual;

    RE
    --
    ab

    Is it the one you want?

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    create table test
    (txt varchar2(10) ) ;

    insert into test values ( 'abc' || chr(10) || 'def' ) ;

    insert into test values ( 'ab' || chr(10) || 'dvdv' ) ;

    i need to identify position in txt and then delete that character

    before
    abc chr(10) def
    ab chr(10) dvdv

    after
    abcdef
    abdvdv

    select replace ( instr(txt,chr(10),1), '' ) from test ;

    output
    4
    3


    update test
    set txt = substr ( txt , 1 , replace(instr(txt,chr(10),1),'')-1 ) || substr ( txt , replace(instr(txt,chr(10),1),'') +1 )
    where instr (txt , chr(10) , 1 ) > 0

    output
    -------
    abcdef
    abdvdv

    could anybody point out some bug in the above statement .
    like
    1) one to one relationship is maintained
    2) do i need to add anything in the where clause
    3) what if the above string contains more than 1 control character of same type .


    what if i find multiple characters chr(10) in a single row .
    i mean

    abc chr(10) def chr(10) ghi

    looking for your advise ..
    Last edited by prakashs43; 02-25-2004 at 01:40 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width