This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
dev:sql:functions:trim [2010/07/02 09:02] tdo |
dev:sql:functions:trim [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
{{page>:top_add&nofooter&noeditbtn}} | {{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}} | {{page>:sql_bar&nofooter&noeditbtn}} | ||
+ | ====== Omnidex SQL Function: TRIM ====== | ||
===== Description ===== | ===== 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. | 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 ===== | ===== Syntax ===== | ||
- | TRIM([<LEADING | TRAILING | BOTH>]['character'][FROM] column | 'string') | + | |
+ | TRIM( character_expression ) | ||
+ | |||
+ | TRIM( <LEADING | TRAILING | BOTH> ['character'] FROM character_expression ) | ||
+ | |||
+ | == 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 == | == LEADING | TRAILING | BOTH == | ||
- | 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. | + | Optional. Specifies if the leading, trailing or both leading and trailing pad characters will be removed from a string. BOTH is the default. |
== 'character' == | == 'character' == | ||
- | Optional. Specify the pad character that is to be removed. If omitted, white space will be removed. | + | Optional. Specify the pad character that is to be removed. If omitted, spaces will be removed. |
- | == FROM == | + | ===== Examples ===== |
- | + | ==== Example 1: Column ==== | |
- | DO NOT USE if LEADING, TRAILING, or BOTH keyword was NOT specified. | + | <code> |
+ | > select trim(name) from individuals; | ||
- | == string == | + | TRIM(INDIVIDUALS.NAME) |
- | + | -------------------------------------------------- | |
- | Required. The string that will be "trimmed". | + | 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}} | ||
- | ===== Examples ===== | ||
- | ==== Example 1 ==== | ||
- | select company, trim(contact) from customers where company='systems' | ||
- | ==== Example 2 ==== | ||
- | select company, trim(LEADING FROM contact) from customers where company='systems' | ||
- | ==== Example 3 ==== | ||
- | select company, trim(TRAILING ',' FROM contact) from customers where company='systems' | ||
{{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||
+ | |||
+ | |||
+ |