- CHR - Returns the character value given character number.
select CHR(37) a, CHR(100) b, CHR(110) c from dual;
/*RETURNS:
A B C
- - -
% d n*/
- CONCAT - Returns string1 concatenated with string2.
select CONCAT('monkey','boy') from dual;
/*RETURNS:
monkeyboy*/
- INITCAP - Returns the string with the first character in every word capatilzed and the rest lowercase.
select INITCAP('i am the walrus') "animal" from dual;
/*RETURNS:
animal
---------------
I Am The Walrus */
- LOWER - Returns the string in all lowercase
select LOWER ('SHOUT IT SHOUT IT OUT LOUD') "lyric" from song;
/*RETURNS:
lyric
---------------
shout it shout it out load
- LPAD - In Oracle/PLSQL, the lpad function pads the left-side of a string with a specific set of characters.
select lpad ('tech', 7) from foo; /*would return ' tech'*/
select lpad ('tech', 2) from foo; /*would return 'te'*/
select lpad ('tech', 8, '0') from foo; /*would return '0000tech'*/
select lpad ('tech on the net', 15, 'z') from foo; /*would return 'tech on the net'*/
select lpad ('tech on the net', 16, 'z') from foo; /*would return 'ztech on the net'*/
- LTRIM - removes character from the left side of the string
select ltrim (' foo') from foo; /*would return 'foo'*/
select ltrim (' foo', ' ') from foo; /*would return 'foo'*/
select ltrim ('000123', '0') from foo; /*would return '123'*/
select ltrim ('123123foo', '123') from foo; /*would return 'foo'*/
select ltrim ('123123foo123', '123') from foo; /*would return 'foo123'*/
- NLS_INITCAP - sets initial letter to capital
select nls_initcap('JOE GAKENHEIMER', 'nls_sort = XGerman') from person;
/*returns 'Joe gakenheimer'*/
- NLS_LOWER - sets initial character to lower case
select nls_initlower('JOE GAKENHEIMER', 'nls_sort = XGerman')
from person;
/*returns 'jOE GAKENHEIMER'*/
- NLS_UPPER - sets enter string to upper case
select nls_initupper('joE GakenHeimer', 'nls_sort = XGerman')
from person;
/*returns 'JOE GAKENHEIMER'*/
- REPLACE - the replace functions allows you to replace all occurences of one string with another.
replace ('This is a test', 'is', 'was') /*would return 'This was a test'*/
replace ('This is a test', 'i', 'a')
/*would replace all occurences of 'i' characters with 's' characters*/
- RPAD - the rpad function pads the right-side of a string with a specific set of characters
rpad ('foo', 5);
/*would return 'foo '*/
rpad ('foo', 2);
/*would return 'fo'*/
rpad ('foo', 8, '0');
/*would return 'foo00000'*/
rpad ('foo is good', 10, 'a');
/*would return 'foo is good'*/
rpad ('foo is good', 11, 'a');
/*would return 'foo is gooda'*/
- RTRIM - removes character from the right side of the string
rtrim ('foo '); /*would return 'foo'*/
rtrim ('foo', 'f'); /*would return 'oo'*/
rtrim ('foo', '0'); /*would return 'foo'*/
rtrim ('foo is good', 'd'); /*would return 'foo is goo'*/
rtrim ('foo is good', 'foo'); /*would return 'is good'*/
- SOUNDEX - the soundex function returns a phonetic representation (the way it sounds) of a string.
soundex ('apples');
/*would return 'A142'*/
soundex ('apples pie');
/*would return 'A142'*/
- SUBSTR - the substr functions allows you to extract a substring from a string.
substr (string, start_position, [length])
soundex ('apples');
/*would return 'A142'*/
soundex ('apples pie');
/*would return 'A142'*/
- SUBSTRB - the substrb functions allows you to extract a substring from bytes.
, so it is just like substr but extracts bytes instead of character strings
- TRANSLATE - the translate function replaces a sequence of characters in a string with another set of characters; it replaces a single character, one at a time; i.e. it will replace the 1st character in the "string to replace" with the 1st character in the "replacement string", then it will replace the 2nd character in the "string to replace" with the 2nd character in the "replacement string" ect...
syntax: translate (string1, string_to_replace, replacement_string )
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.
translate ('12pizza3', '123', '456);
/*would return '45pizza6'*/
translate ('footy, 'foo', 'git');
/*would return 'gitty'*/
- UPPER - converts all letters in the specified string to uppercase
upper('i am shouting')/*returns: 'I AM SHOUTING'*/
- ASCII - gets the ascii value of a charater
select ascii('A') from foo;
- ASCIISTR - Converts An ASCII String To An ASCII String In The Database's Character Set
select asciistr(chr(128) || 'Hello' || chr(255)) from person;
- INSTR - returns the location of a substring in a string
instr ('Hang the DJ', 'a');
/*would return 2;the first occurrence of 'a'*/
instr ('Hand in Glove', 'n', 1, 2);
/*would return 7; the second occurrence of 'n'*/
- INSTRB - returns the location of a string of bytes within a substring
- LENGTH - the length of a given substring
- LENGTHB - returns the length of a string of bytes with in a substring
- NLSSORT - returns the string of bytes used to sort a string
CREATE TABLE person (name VARCHAR2(15));
INSERT INTO person VALUES ('Smith');
INSERT INTO person VALUES ('Smith');
INSERT INTO person VALUES ('Smith');
COMMIT;
SELECT * FROM person ORDER BY name;
SELECT * FROM person
ORDER BY NLSSORT(person, 'NLS_SORT = XDanish');