SAND CDBMS SQL Reference Guide
Value Expressions

Functions

String Functions

The string functions are used to manipulate character string values. A string function can be used anywhere a string value expression is permitted. SAND CDBMS supports the following string functions:

CONCAT ( value-expression1, value-expression2 )
The CONCAT function is the explicit function counterpart to the concatenation operator ("||"). CONCAT() returns the character string produced by concatenating the first argument with the second argument. An error is generated if either of the input arguments does not evaluate to a character string. The maximum size of the result string is 4056 characters.

Example

Input	Output
'hello', 'world'	'helloworld'

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES (CONCAT('hello', 'world'));
1 row affected
SELECT col1 FROM string_table;
1 row selected
col1
--------------------
helloworld


GENKEY ( value-expression1 [ { , value-expression<2-10> }... ] )
Returns a 19-character string that is unique for each unique input set. The function accepts up to 10 value expressions as arguments, producing a "fingerprint" of that data.

Example

Input	Output
'a127', 6348.57, '2004-02-09'	_? ,^Jz<-+VMw9+T_"3

CREATE TABLE transact_store (tid CHAR(19), cust_id CHAR(4), tval DEC(10,2), tdate DATE, UNIQUE(cust_id, tval, tdate));
INSERT INTO transact_store VALUES ( NULL, 'a127', 6348.57, '2004-02-09');
1 row affected
UPDATE transact_store SET tid = GENKEY(cust_id, tval, tdate);
1 row affected
SELECT tid FROM transact_store;
1 row selected
tid
-------------------
_? ,^Jz<-+VMw9+T_"3


LEFT ( value-expression, length )
Returns a substring from value-expression consisting of the number of leftmost characters indicated by length. The length parameter is a numeric expression that evaluates to an integer value. If the length value is greater than or equal to the length of the input string, the whole string is returned.

Example

Input	Output
'isopropyl', 3	'iso'

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES (LEFT('isopropyl', 3));
1 row affected
SELECT col1 FROM string_table;
1 row selected
col1
--------------------
iso


LEN ( value-expression )
LENGTH ( value-expression )
Returns the number of characters in the input expression.

Example

Input	Output
'sea bass'	8

CREATE TABLE string_table (col1 VARCHAR(10));
INSERT INTO string_table VALUES ('sea bass');
1 row affected
SELECT LENGTH(col1) FROM string_table;
1 row selected
1
-----------
          8


LOWER ( value-expression )
LCASE ( value-expression )
Returns the input expression with all uppercase characters converted to their lowercase equivalents.

Example

Input	Output
'LOUD'	'loud'

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES (LOWER('LOUD'));
1 row affected
SELECT col1 FROM string_table;
1 row selected
col1
--------------------
loud


LTRIM ( value-expression )
Returns the input string with leading spaces removed.

Example

Input	Output
'    snipped'	'snipped'

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES (LTRIM('    snipped'));
1 row affected
SELECT col1 FROM string_table;
1 row selected
col1
--------------------
snipped


POSITION ( value-expression1 IN value-expression2 )
POSSTR ( value-expression2, value-expression1 )
Searches for and returns the starting position of one string (value-expression1) within another string (value-expression2). Counting in value-expression2 is from left to right, starting at position 1. If the string is not found, 0 is returned.

Note that the order of parameters for POSSTR is opposite that for POSITION.

Examples

Input	Output
'ice', 'solstice'	6
'fun', 'malfunction'	4

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES ('solstice');
1 row affected
SELECT POSSTR(col1, 'ice') FROM string_table;
1 row selected
1
-----------
          6
DELETE FROM string_table;
1 row affected
INSERT INTO string_table VALUES ('malfunction');
1 row affected
SELECT POSITION('fun' IN col1) FROM string_table;
1 row selected
1
-----------
          4


RIGHT ( value-expression, length )
Returns a substring from value-expression consisting of the number of rightmost characters indicated by length. The length parameter is a numeric expression that evaluates to an integer value. If the length value is greater than or equal to the length of the input string, the whole string is returned.

Example

Input	Output
'isopropyl'	'pyl'

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES ('isopropyl');
1 row affected
SELECT RIGHT(col1, 3) FROM string_table;
1 row selected
1
---
pyl


RTRIM ( value-expression )
Returns the input string with trailing spaces removed.

Example

Input	Output
'pruned      '	'pruned'

CREATE TABLE string_table (col1 CHAR(20));
INSERT INTO string_table VALUES ('pruned      ');
1 row affected
SELECT RTRIM(col1) FROM string_table;
1 row selected
1
--------------
pruned


SUBSTRING ( value-expression, start-position [, length ] )
SUBSTRING ( value-expression FROM start-position [ FOR length ] )
SUBSTR ( value-expression, start-position [, length ] )
SUBSTR ( value-expression FROM start-position [ FOR length ] )
Returns the specified portion of the input expression. Value-expression is the input character string; start-position is the starting position of the substring, counting from the left (the first character in the input string is position 1, the second character is position 2, and so on); and length is the number of characters to return, counting from left to right from the substring starting position. If the length argument is omitted, the substring from the starting position to the end of the input string is returned.

Examples

Input	Output
'12345', 2, 2	'23'
'PK1-H4V1Y9-QC', 5, 6	'H4V1Y9'
'514-939-3477', 5	'939-3477'

CREATE TABLE string_table (col1 SMALLINT, col2 VARCHAR(20));
INSERT INTO string_table VALUES (1, '12345');
1 row affected
INSERT INTO string_table VALUES (2, 'PK1-H4V1Y9-QC');
1 row affected
INSERT INTO string_table VALUES (3, '514-939-3477');
1 row affected
SELECT SUBSTR(col2, 2, 2) FROM string_table WHERE col1=1;
1 row selected
1
--
23
SELECT SUBSTRING(col2 FROM 5 FOR 6) FROM string_table WHERE col1=2;
1 row selected
1
------
H4V1Y9
SELECT SUBSTR(col2, 5) FROM string_table WHERE col1=3;
1 row selected
1
----------------
939-3477


TRIM ( [ [ LEADING | TRAILING | BOTH ] [ character ] FROM ] value-expression )
Trims the specified character (character) from the beginning and/or end of the input string (value-expression) and returns the resulting string. The default trim action is BOTH, and the default character value is a blank space. Only a single character can be specified for character. If the specified leading/trailing character is not found, the input expression is returned unchanged.

Examples

Input	Output
'   midriff  '	'midriff'
TRAILING, '   midriff  '	'   midriff'
LEADING 's' FROM 'ssssSmokin'	'Smokin'

CREATE TABLE string_table (col1 CHAR(20));
INSERT INTO string_table VALUES ('   midriff  ');
1 row affected
SELECT TRIM (col1) FROM string_table;
1 row selected
1
--------------------
midriff
SELECT TRIM(TRAILING FROM col1) FROM string_table;
1 row selected
1
--------------------
   midriff
SELECT TRIM(LEADING 's' FROM 'ssssSmokin') FROM string_table;
1 row selected
1
----------
Smokin


UPPER ( value-expression )
UCASE ( value-expression )
Returns the input expression with all lowercase characters converted to their uppercase equivalents.

Example

Input	Output
'sotto voce'	'SOTTO VOCE'

CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES ('sotto voce');
1 row affected
SELECT UPPER(col1) FROM string_table;
1 row selected
1
--------------------
SOTTO VOCE