RE: Trimming strings in SQL
According to the Informix Guide to SQL: Syntax manual,
1. "The LENGTH function returns the number of bytes in a character column,
not including any trailing spaces."
2. "The OCTET_LENGTH function returns the number of bytes in a character
column, including any trailing spaces."
-----Original Message-----
From: Olcay Sarioglu [mailto:olcay@knidos.cc.metu.edu.tr]
Sent: Saturday, October 31, 1998 02:56
To: asalerno@my-dejanews.com
Cc: informix-list@iiug.org
Subject: Re: Trimming strings in SQL
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