This will list the different functions that area available in the database environment.
Name
|
Description
|
In String
|
Checks to see if a string in a sub-string inside another string
Microsoft:
Oracle:
Function: INSTR('HelloWorld', 'W')
Result: 6
|
Sub String
|
Returns specified characters from character value string.
Microsoft:
Oracle: The syntax for the SUBSTR function in Oracle/PLSQL is:
SUBSTR( string, start_position [, length ] )
Parameters or Arguments - String -> The source string.
- Start_position -> The starting position for extraction. The first position in the string is always 1.
- Llength -> Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.
Example:
Function: SUBSTR('HelloWorld', 1, 5)
Result: Hello
NOTE - If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
- If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
- If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
- If length is a negative number, then the SUBSTR function will return a NULL value.
- See also the REGEXP_SUBSTR function.
|
| Lower Case |
Converts alpha character values to lower case
MicroSoft:
Oracle:
Function: LOWER('SQL Course')
Result: sql course
|
| Upper Case |
Converts alpha character values to upper case
Microsoft:
Oracle:
Function: UPPER('SQL Course')
Result: SQL COURSE
|
| Initial Capital Word |
Converts alpha character values to upper case for the first letter of each word; all other letters are in lower case
Microsoft:
Oracle:
Function: INITCAP('SQL COURSE')
Result: Sql Course |
| Concatenation |
Concatenates the first character value to the second character value;
Microsoft:
Oracle:
Function: CONCAT('Hello', 'World')
Result: HelloWorld
Note: You are limited to using 2 parameters for this function.
Function: 'Hello' || 'World'
Result: HelloWorld |
| Length |
Returns the number of characters in an expression
Microsoft:
Oracle:
Function: LENGTH('HelloWorld')
Result: 10 |
| Left Pad |
Returns an expression left-padded to a certain length of characters with a character expression.
Microsoft:
Oracle:
Function: LPAD('Hello', 10, '*')
Result: '*****Hello' |
| Right Pad |
Returns an expression right-padded to a certain length of characters with a character expression.
Microsoft:
Oracle:
Function: RPAD('Hello', 10, '*')
Result: 'Hello*****' |
| Trim |
Enables you to trim leading or trailing characters from a character string.
Microsoft:
Oracle:
Function: TRIM('H' FROM 'HelloWorld')
Result: 'elloWorld' |
| Replace |
Searches a text expression for a character string and if found, replaces it with the specified replacement string.
Microsoft:
Oracle:
Function: REPLACE('Jack and Jue', 'J', 'BL')
Result: 'BLACK and BLUE' | |
|
