Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered: Removing last character

    Hi..

    Me back again with yet another query that I am clueless about at this moment.

    What I wanted to achieve is removing the last character from a string.

    I though of using SUBSTRING or SUBSTR functions for this.

    Here is how my query looks like:
    Code:
    SELECT FIRST 1 my_column, SUBSTRING(my_column FROM 1 FOR CHAR_LENGTH(my_column)-1)
    FROM my_table
    But this does not work. It returns the whole original string (both fields returned by the select are the same). I checked if there were trailing blanks that might have had caused this issue but no.. there weren't any (I would use TRIM to remove them as well in case there were). I then thought of using SUBSTR but it also did not help.

    Why isn't the FOR part reducing 1 from the character length of the field?

    Can anyone please suggest what I might be doing wrong or if there's another approach to achieving this? Thanks for any help! Regards.

  2. #2
    Join Date
    Jan 2006
    Posts
    38
    Sorry, a careless mistake of mine.

    Actually my query was:
    Code:
    SELECT FIRST 1 my_column, SUBSTRING(my_column FROM 4 FOR CHAR_LENGTH(my_column)-1)
    FROM my_table
    And what I was doing wrong was in the FOR part. It should not have had been CHAR_LENGTH(my_column)-1 but CHAR_LENGTH(my_column)-4. Also, the trim function is important since the column was a char[10] type. Hence the corrected query would be:
    Code:
    SELECT FIRST 1 my_column, SUBSTRING(my_column FROM 4 FOR (CHAR_LENGTH(TRIM(my_column))-4))
    FROM my_table
    There may be syntax errors (mismatching braces) but that does reflect the jist of it.. the logical flaw in my original query. Regards.

Posting Permissions

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