Re: Trimming strings in SQL

I have run the following SQL: select trim(" abc "), length(trim(" abc ")), length(trim(leading '#' from "#abc##")), length(trim(trailing '#' from "#abc##")) from dual and it returned : (constant) (constant) (constant) (constant) abc 3 5 4 This is correct. But when I change '#' to blank select trim(" abc "), length(trim(" abc ")), length(trim(leading ' ' from " abc ")), length(trim(trailing ' ' from " abc ")) from dual it returns (constant) (constant) (constant) (constant) abc 3 3 4 I think there is something wrong with the LEADING command. It also trims from the tail of the string. == Olcay Sarioglu INFORMIX-OnLine Dynamic Server Version 7.24.UC5 AIX 4.1.4 On Fri, 30 Oct 1998 asalerno@my-dejanews.com wrote: > In article <Pine.BSI.4.05L.9810300742310.16325-100000@mail.his.com>, > Darrel Davis <darreld@mail..his.com> wrote: > > Does Informix SQL have any comparable commands > > like Oracle's LTRIM and RTRIM? Is there another > > way to get trimmed strings is SQL. I know about > > 'clipped' in 4GL but we need to be able to do it > > in SQL. > > Directly from the manual, such an amazing thing :-) > > >>Use the TRIM() function to remove leading or trailing (or both) pad characters > >>from a string. The TRIM() function returns a VARCHAR string that is identical > >>to the character string passed to it, except that any leading or trailing pad > >>characters, if specified, are removed. If no trim specification (LEADING, > >>TRAILING, or BOTH) is specified, then BOTH is assumed. If no trim character > >>value expression is used, a single space is assumed. If either the trim > >>character value expression or the source character value expression evaluates > to null, the > >>result of the trim function is null. The maximum length of the resultant > string > >>must be 255 or less, because the VARCHAR data type supports only > >>255 characters. > >> > >>Some generic uses for the TRIM() function are shown in the following > >>example: > >>SELECT TRIM (c1) FROM tab; > >>SELECT TRIM (TRAILING '#' FROM c1) FROM tab; > >>SELECT TRIM (LEADING FROM c1) FROM tab; > >>UPDATE c1='xyz' FROM tab WHERE LENGTH(TRIM(c1))=5; > >>SELECT c1, TRIM(LEADING '#' FROM TRIM(TRAILING '%' FROM> '###abc%%%')) FROM tab; > > RTFM Much? > > Tony > -- > asalerno@monmouth.com > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own >