Differences

This shows you the differences between two versions of the page.

--- dev:sql:functions:trim [2009/12/05 00:48]
tdo created
+++ dev:sql:functions:trim [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Function: TRIM ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Function: TRIM ======
 ===== Description =====
 The TRIM function removes any leading and/or trailing pad characters from a string. The pad character is a single character and defaults to a space character.
+This function returns a STRING datatype.
 ===== Syntax =====
-TRIM([<LEADING | TRAILING | BOTH>][pad][FROM] string)
-== LEADING | TRAILING | BOTH ==
+TRIM( character_expression )
-Optional. Specifies if the leading, trailing or both leading and trailing pad characters will be removed from a string. If omitted, the FROM keyword must also be omitted. BOTH is the default.
-== pad ==
+TRIM( <LEADING | TRAILING | BOTH> ['character'] FROM character_expression )
-Optional. Specify the pad character that is to be removed. If omitted, white space will be removed.
-== FROM ==
+== character_expression ==
+Required.  Any expression that returns a character-class datatype.  Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype.
+== LEADING | TRAILING | BOTH ==
-DO NOT USE if LEADING, TRAILING, or BOTH keyword was NOT specified.
+Optional. Specifies if the leading, trailing or both leading and trailing pad characters will be removed from a string. BOTH is the default.
-== string ==
+== 'character' ==
-Required. The string that will be "trimmed".
+Optional. Specify the pad character that is to be removed. If omitted, spaces will be removed.
 ===== Examples =====
-==== Example 1 ====
+==== Example 1: Column ====
-select company, trim(contact) from customers where company='systems'
+<code>
-==== Example 2 ====
+> select trim(name) from individuals;
-select company, trim(LEADING FROM contact) from customers where company='systems'
-==== Example 3 ====
+TRIM(INDIVIDUALS.NAME)
-select company, trim(TRAILING ',' FROM contact) from customers where company='systems'
+--------------------------------------------------
+MS. MARY LEWIS
+MRS. ALICE R TOLLIVER
+MR. SIDNEY K TOLLIVER
+MRS. JENNIFER J HOPKINS
+MR. CHARLIE HOPKINS
+MS. KIMBERLY BLUE
+KATHLEEN TUCKER
+</code>
+==== Example 2: Column with LEADING ====
+<code>
+> select individual, trim(leading '0' from individual) ind from individuals;
+INDIVIDUAL    IND
+------------  ------------
+000900011022  900011022
+000900047761  900047761
+000900047762  900047762
+000900067115  900067115
+000900067114  900067114
+000900215567  900215567
+000900240828  900240828
+</code>
+==== Example 3: Complex expression ====
+The following complex example creates a string containing the name and an optional email.
+<code>
+> Select        (((trim($proper(NAME)) ||
+>>               case
+>>                 when EMAIL > ' ' then ' ('
+>>                 else ''
+>>                 end) ||
+>>               trim(EMAIL)) ||
+>>               case
+>>                 when EMAIL > ' ' then ')'
+>>                 else ''
+>>                 end) name_and_email
+>>   from        INDIVIDUALS;
+NAME_AND_EMAIL
+-----------------------------------------------------------------------------
+Ms. Mary Lewis
+Mrs. Alice R Tolliver (alicetolliver@securitasinc.com)
+Mr. Sidney K Tolliver (sidneytolliver@deere.com)
+Mrs. Jennifer J Hopkins
+Mr. Charlie Hopkins
+Ms. Kimberly Blue
+Kathleen Tucker
+</code>
+====== Additional Resources ======
+See also:
+{{page>dev:sql:functions:see_also_string&nofooter&noeditbtn&noheader}}
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/functions/trim.1259974105.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes