|
ASCII
|
|
Get The ASCII Value Of A Character
|
ASCII(<string_or_column>)
|
SELECT ASCII('A') FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;
|
| |
|
CASE Related Functions
|
|
Upper Case
|
UPPER(<string_or_column>)
|
|
SELECT UPPER('Dan Morgan') FROM dual;
|
|
Lower Case
|
LOWER(<string_or_column>)
|
|
SELECT LOWER('Dan Morgan') FROM dual;
|
|
Initial Letter Upper Case
|
INITCAP(<string_or_column>)
|
|
SELECT INITCAP('DAN MORGAN') FROM dual;
|
|
NLS Upper Case
|
NLS_UPPER(<string_or_column>)
|
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
|
|
NLS Lower Case
|
NLS_LOWER(<string_or_column>)
|
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;
|
|
NLS Initial Letter Upper Case
|
NLS_INITCAP(<string_or_column>)
|
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;
|
| |
|
CHR
|
|
Character
|
CHR(<ascii_string_or_column>>)
|
SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;
SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual;
|
| |
|
COALESCE
|
|
Returns the first non-null occurrence
|
COALESCE(<value>, <value>, <value>, ...)
|
CREATE TABLE test (
col1 VARCHAR2(1),
col2 VARCHAR2(1),
col3 VARCHAR2(1));
INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');
SELECT COALESCE(col1, col2, col3) FROM test;
|
| |
|
CONCAT
|
|
Concatenate
|
CONCAT(<first_string_or_column>>, <second_string_or_column>>)
|
|
SELECT CONCAT('Dan ', 'Morgan') FROM dual;
|
| |
|
CONVERT
|
|
Converts From One Character Set To Another
|
CONVERT(<character>,<destination_character_set>,
<source_character_set>)
|
SELECT CONVERT('?????A B C D E','US7ASCII','WE8ISO8859P1')
FROM dual;
|
| |
|
INSTR
|
|
See links at page bottom
|
| |
|
LENGTH
|
|
String Length
|
LENGTH(<string_or_column>)
|
|
SELECT LENGTH('Dan Morgan') FROM dual;
|
| |
|
LPAD
|
|
Left Pad
|
LPAD(<string_or_column>, <final_length>, <padding_character>)
|
|
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
|
| |
|
LTRIM
|
|
Left Trim
|
LTRIM(<string_or_column>)
|
|
SELECT LTRIM(' Dan Morgan ') FROM dual;
|
| |
|
NLSSORT
|
Returns the string of bytes used to sort a string.
The string returned is of RAW data type
|
NLSSORT(<column_name>, 'NLS_SORT = <NLS Parameter>);
|
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('G鈈erd');
COMMIT;
SELECT * FROM test ORDER BY name;
SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
|
| |
|
REPLACE
|
|
See links at page bottom
|
| |
|
REVERSE
|
|
Reverse
|
REVERSE(<string_or_column>)
|
SELECT REVERSE('Dan Morgan') FROM dual;
SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;
|
| |
|
RPAD
|
|
Right Pad
|
RPAD(<string_or_column>, <final_length>, <padding_character>)
|
|
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
|
| |
|
RTRIM
|
|
Right Trim
|
RTRIM(<string_or_column>)
|
|
SELECT RTRIM(' Dan Morgan ') FROM dual;
|
| |
|
SOUNDEX
|
Returns Character String Constaining The Phonetic Representation Of Another String
|
Rules:
-
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y
-
Assign numbers to the remaining letters (after the first) as
follows:
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
-
If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
-
Return the first four bytes padded with 0.
SOUNDEX(<string_or_column>)
|
CREATE TABLE test (
name VARCHAR2(15));
INSERT INTO test VALUES ('Smith');
INSERT INTO test VALUES ('Smyth');
INSERT INTO test VALUES ('Smythe');
INSERT INTO test VALUES ('Smither');
INSERT INTO test VALUES ('Smidt');
INSERT INTO test VALUES ('Smick');
INSERT INTO test VALUES ('Smiff');
COMMIT;
SELECT * FROM test;
SELECT *
FROM test
WHERE SOUNDEX(name) = SOUNDEX('SMITH');
|
| |
|
SUBSTR
|
|
See links at page bottom
|
| |
|
TRANSLATE
|
|
See links at page bottom
|
| |
|
TREAT
|
|
Changes The Declared Type Of An Expression
|
TREAT (<expression> AS REF schema.type))
|
SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p;
|
| |
|
TRIM (variations are LTRIM and RTRIM)
|
|
Trim Spaces
|
TRIM(<string_or_column>)
|
SELECT ' Dan Morgan ' FROM dual;
SELECT TRIM(' Dan Morgan ') FROM dual;
|
|
Trim Other Characters
|
TRIM(<character_to_trim> FROM <string_or_column>)
|
|
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
|
|
Trim By CHR value
|
TRIM(<string_or_column>)
|
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;
SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;
|
| |
|
Vertical Bars
|
|
Also known as Pipes
|
<first_string> || <second_string>
|
SELECT 'Dan' || ' ' || 'Morgan' FROM dual;
with alias
SELECT 'Dan' || ' ' || 'Morgan' NAME FROM dual;
or
SELECT 'Dan' || ' ' || 'Morgan' AS NAME FROM dual;
|
| |
|
VSIZE
|
|
Byte Size
|
VSIZE(<string_or_column>)
|
|
SELECT VSIZE('Dan Morgan') FROM dual;
|