Differences

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

--- dev:sql:functions:concatenation [2009/12/04 22:54]
tdo
+++ dev:sql:functions:concatenation [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: CASE ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Function: || (Concatenation) ======
 ===== Description =====
-The || (two vertical bars) function returns a single concatenated character string made up of two or more character strings. CHAR||CHAR will return a CHAR string; everything else will return a C STRING.
+The concatenation operator - || (two vertical bars) - returns a single concatenated character string made up of two or more character expressions. All blanks are preserved during concatenation. Use the TRIM function to eliminate blanks if desired.
+A concatenation operator on two character strings (CHARACTER_DATATYPE_COLUMN1 || CHARACTER_DATATYPE_COLUMN2) will return a CHARACTER string; everything else will return a STRING.
-All blanks are preserved during concatenation. Use the TRIM function to eliminate blanks if desired.
 ===== Syntax =====
-  [ < column_spec | "string" > || < column_spec | "string " >
-      [ || <column_spec | "string "[...] ]
+  character_expression || character_expression [ || character_expression ...]
 ===== Examples =====
-==== Example 1: Column + text + Column ====
+==== Example 1: Concatenating both columns and text ====
-<code SQL>
+<code>
-select company,
+> select 'Individual: ' || individual || ' Name: ' || name first_line from individuals;
-CUSTOMERS.LASTNAME || ', ' || CUSTOMERS.FIRSTNAME
-from customers
+FIRST_LINE
-where company = 'systems'
+-----------------------------------------------------------------------------
-</code>
+Individual: 000900011022 Name: MS. MARY LEWIS
-==== Example 2 ====
+Individual: 000900047761 Name: MRS. ALICE R TOLLIVER
-<code SQL>
+Individual: 000900047762 Name: MR. SIDNEY K TOLLIVER
-select
+Individual: 000900067115 Name: MRS. JENNIFER J HOPKINS
-PRODUCTS.PRODUCT_NO || ' is the product number for ' || PRODUCTS.DESCRIPTION
+Individual: 000900067114 Name: MR. CHARLIE HOPKINS
-from products
+Individual: 000900215567 Name: MS. KIMBERLY BLUE
+Individual: 000900240828 Name: KATHLEEN TUCKER</code>
+==== Example 2: 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/concatenation.1259967293.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes