Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Unanswered: how do i trim spaces from a string ?

    hi,

    suppose i have the following string :
    'abc '.
    how do i trim the right spaces ?

    thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: how do i trim spaces from a string ?

    RTRIM Function

    Cheers
    Sathyaram

    Originally posted by prf
    hi,

    suppose i have the following string :
    'abc '.
    how do i trim the right spaces ?

    thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    5
    thanks ,
    i already knew that but no matter what i try RTRIM fails.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    RTRIM function must work.
    Do you get any error message?
    Are you sure that there are spaces? Try to export the the result to TXT file to see if there are some spaces.

    Sample on Windows:
    export to c:\temp\file_name.del of del select * from table_name

    Maybe you need to substring the column. Try SUBSTR function.

    Grofaty

  5. #5
    Join Date
    Aug 2003
    Posts
    5
    here is what im running . without the RTRIM call its working.

    CREATE PROCEDURE PRF_P_DELETE_USER(IN USER CHAR(20))
    LANGUAGE SQL
    RESULT SETS 0
    BEGIN
    DECLARE STMT VARCHAR(1000);
    RTRIM(USER);
    SET STMT = 'NET USER ' || USER || ' /DEL';
    CALL SHELL_EXEC(STMT);
    END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "RTRIM" was found following "TMT VARCHAR(1000);
    ". Expected tokens may include: "<psm_return>". LINE NUMBER=6.
    SQLSTATE=42601

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Firstly...

    SET USER = RTRIM(USER);

    ...is the correct syntax.

    Secondly, USER is a char field and will always be 20 bytes. Use varchar and you won't need to trim it in the first place.

  7. #7
    Join Date
    Aug 2003
    Posts
    5
    Originally posted by Damian Ibbotson
    Firstly...

    SET USER = RTRIM(USER);

    ...is the correct syntax.

    Secondly, USER is a char field and will always be 20 bytes. Use varchar and you won't need to trim it in the first place.
    thanks.
    i forgot to say that im a noob.

  8. #8
    Join Date
    Oct 2011
    Posts
    1
    You could also perform a cast on the column

  9. #9
    Join Date
    Apr 2012
    Posts
    32

    Trimming leading, trailing AND white spaces in the middle

    Hello all

    I need to ensure that my output string does NOT contain leading and trailing spaces. Further, within the string, any two consecutive white spaces whould be rediced to one space. Thus for example if I have 'TWO MEN' as my string I need to have 'TWO MEN' as my result.

    Is this possible with the TRIM function or any other function?

    P. S. I do not have a test table / database at my disposal and thus can't check and verify for myself.

    Thanks

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Trimming leading, trailing spaces: Use TRIM function.

    Two or more consecutive spaces to reduce one space: Use three nested REPLACE functions.
    Step 1: ' ' (one blank) ---> '<>'
    Step 2: '><' ---> '' (remove it)
    Step 3: '<>' ---> ' ' (one blank)
    Note: I saw this technique in an atricle on web. But, I couldn't remember where the article was.


    Example: a colon(:) was added at the end of output string to show that trailing blanks were removed.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT given_string
         , REPLACE(
              REPLACE(
                 REPLACE(
                    TRIM(given_string)
                  , ' ' , '<>'
                 )  /* End REPLACE */
               , '><' , ''
              )  /* End REPLACE */
            , '<>' , ' '
           )  /* End REPLACE */
           || ':' AS output_string
     FROM (VALUES 'TWO  MEN'
                , ' one  two   three    four     '
          ) AS t(given_string)
    ;
    ------------------------------------------------------------------------------
    
    GIVEN_STRING                   OUTPUT_STRING                                                
    ------------------------------ -------------------------------------------------------------
    TWO  MEN                       TWO MEN:                                                     
     one  two   three    four      one two three four:                                          
    
      2 record(s) selected.
    Last edited by tonkuma; 04-12-12 at 11:23. Reason: Add short description(Step 1 to Step 3).

  11. #11
    Join Date
    Nov 2015
    Posts
    1
    Nice Logic.. thank you

Posting Permissions

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