Re: Trimming strings in SQL

Hi, It's a feature. The length() function counts the string length without trailing blanks. Bye, Octav Olcay Sarioglu wrote: > >select length(" abc ") >from dual > >returns 4, not 6. Why? > > > >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 >> > >== Olcay Sarioglu >== olcay@knidos.cc.metu.edu.tr http://www.cc.metu.edu.tr/~olcay >== Computer Center, Middle East Tech.Univ. Ankara TURKIYE >== Voice: +90 312 2103381 Fax: +90 312 2101120 > -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com