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:
SELECT FIRST 1 my_column, SUBSTRING(my_column FROM 1 FOR CHAR_LENGTH(my_column)-1)
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.
SELECT FIRST 1 my_column, SUBSTRING(my_column FROM 4 FOR CHAR_LENGTH(my_column)-1)
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 type. Hence the corrected query would be:
SELECT FIRST 1 my_column, SUBSTRING(my_column FROM 4 FOR (CHAR_LENGTH(TRIM(my_column))-4))
There may be syntax errors (mismatching braces) but that does reflect the jist of it.. the logical flaw in my original query. Regards.