Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    80

    Unanswered: String functions

    hi all,

    I started practicing string functions.

    is there any string function which can replace multiple spaces in data with a single space?
    can any one list the string functions available in oracle with syntax or help me by reffering a source to learn them.

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Here's an inelegant solution:

    UPDATE mytable
    SET field1 = REPLACE(field1, ' ', ' '); --replace two spaces with one space
    COMMIT;

    Run recursively until zero rows updated.

    http://download-west.oracle.com/docs...a96540/toc.htm
    (You'll need to register--free)

  3. #3
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Oops...forgot the WHERE clause:

    WHERE field1 LIKE '% %'

    Without this, you won't know when you're done.

  4. #4
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    If you can choose a character that will never be in your strings, you can do it rwithout recursivity :

    With '*' :

    SQL> select replace(replace(replace(' with only one space ',' ','* '),' *',''),'*','') result from dual;

    RESULT
    ---------------------
    with only one space

    But you need such a character.

    Eric.

Posting Permissions

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