The SQL Functions select list enables the use of special functions that are native to the source database. The following functions are the most common ones:
SQL Function and Source Databases |
Description |
Syntax and Output Example |
---|---|---|
LTRIM (<value>)Oracle, SQL Server |
Removes all leading spaces in the field indicated in the parentheses. |
LTRIM(cmpnt_mfr) cmpnt_mfr = ' Shell' LTRIM(cmpnt_mfr)='Shell' |
RTRIM(<value>)Oracle, SQL Server |
Removes all trailing spaces in the field indicated in the parentheses. |
RTRIM(cmpnt_num) IS NULL cmpnt_num = |
SUBSTRING(<value>,<begin>,<count>)Oracle |
Retrieves a part of the field indicated in the <value> data field from the <begin> position for the number of characters indicated in the <count> field. |
SUBSTRING(cmpnt_mfr,1,4) cmpnt_mfr = 'Shell' SUBSTRING(cmpnt_mfr,1,4)='Shel' |
SUBSTR(<value>,<begin>,<count>) |
Retrieves a part of the field indicated in the <value> data field from the <begin> position for the number of characters indicated in the <count> field. |
SUBSTR (cmpnt_mfr,1,4) cmpnt_mfr = 'Shell' SUBSTR(cmpnt_mfr,1,4)='Shel' |
UPPER(<value>)Oracle, SQL Server |
The upper case format of the contents of the field indicated in the <value> data field. |
UPPER(cpmnt_name) cmpnt_name = '101-aa' UPPER(cmpnt_name)='101-AA |
LOWER(<value>)Oracle, SQL Server |
The lower case format of the contents of the field indicated in the <value> data field. |
LOWER(loop_name) loop_name = '101- AA' LOWER(loop_name)='101-aa |