Removing last character

exterminator · #1 (**permalink**) 04-25-06, 04:55

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.

exterminator · #2 (**permalink**) 04-25-06, 05:47

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech