CHARACTER & STRING FUNCTIONS

ascii

SELECT ascii('t') FROM DUAL -- would return 116.
SELECT ascii('T') FROM DUAL -- would return 84.
SELECT ascii('T2') FROM DUAL -- would also return 84.

chr
select chr(116) from dual -- would return 't'.
select chr(84) from dual -- would return 'T'.


UNISTR
Convert String To The National Character Set (either UTF8 or UTF16)
Unistring Value Resulting character
unistr('\0300') grave accent ( ` )
unistr('\0301') acute accent ( ´ )
unistr('\0302') circumflex ( ^ )
unistr('\0303') tilde ( ~ )
unistr('\0308') umlaut ( ¨ )


compose function returns a Unicode string.

select compose('o' || unistr('\0308') ) from dual -- would return ö
select compose('a' || unistr('\0302') ) from dual -- would return â
select compose('e' || unistr('\0301') ) from dual -- would return é


concat

select concat('Tech on', ' the Net') from dual -- would return 'Tech on the Net'.
select concat('a', 'b') from dual -- would return 'ab'.

|| Operator

select 'Tech on' || ' the Net' from dual -- would return 'Tech on the Net'.
select 'a' || 'b' || 'c' || 'd' from dual -- would return 'abcd'.


dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.

select dump('Tech') from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 10) from dual -- would return 'Typ=96 Len=4: 84,101,99,104'
select dump('Tech', 16) from dual -- would return 'Typ=96 Len=4: 54,65,63,68'
select dump('Tech', 1016) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
select dump('Tech', 1017) from dual -- would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

Value Explanation
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name


initcap

select initcap('tech on the net') from dual -- would return 'Tech On The Net'
select initcap('GEORGE BURNS') from dual -- would return 'George Burns'

upper

select upper('Tech on the Net') from dual -- would return 'TECH ON THE NET'
select upper('george burns 123 ') from dual -- would return 'GEORGE BURNS 123

lower

select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123 '


instr

select instr('Tech on the net', 'e') from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 1) from dual -- would return 2; the first occurrence of 'e'
select instr('Tech on the net', 'e', 1, 2) from dual -- would return 11; the second occurrence of 'e'
select instr('Tech on the net', 'e', 1, 3) from dual -- would return 14; the third occurrence of 'e'


length

select length(NULL) from dual -- would return NULL.
select length('') from dual -- would return NULL.
select length('Tech on the Net') from dual -- would return 15.
select length('Tech on the Net ') from dual -- would return 16.

lower

select lower('Tech on the Net') from dual -- would return 'tech on the net'
select lower('GEORGE BURNS 123 ') from dual -- would return 'george burns 123

lpad function pads the left-side of a string with a specific set of characters.

select lpad('tech', 7) from dual -- would return ' tech'
select lpad('tech', 2) from dual -- would return 'te'
select lpad('tech', 8, '0') from dual-- would return '0000tech'
select lpad('tech on the net', 15, 'z') from dual --would return 'tech on the net'
select lpad('tech on the net', 16, 'z') from dual -- would return 'ztech on the net'

rpad function pads the right-side of a string with a specific set of characters.

select rpad('tech', 7) from dual -- would return 'tech '
select rpad('tech', 2) from dual -- would return 'te'
select rpad('tech', 8, '0') from dual -- would return 'tech0000'
select rpad('tech on the net', 15, 'z') from dual -- would return 'tech on the net'
select rpad('tech on the net', 16, 'z') from dual -- would return 'tech on the netz'


trim

select trim(' tech ') from dual -- would return 'tech'
select trim(' ' from ' tech ') from dual -- would return 'tech'
select trim(leading '0' from '000123') from dual -- would return '123'
select trim(trailing '1' from 'Tech1') from dual -- would return 'Tech'
select trim(both '1' from '123Tech111') from dual -- would return '23Tech'

ltrim

select ltrim(' tech') from dual -- would return 'tech'
select ltrim(' tech', ' ') from dual -- would return 'tech'
select ltrim('000123', '0') from dual -- would return '123'
select ltrim('123123Tech', '123') from dual -- would return 'Tech'
select ltrim('123123Tech123', '123') from dual -- would return 'Tech123'
select ltrim('xyxzyyyTech', 'xyz') from dual -- would return 'Tech'
select ltrim('6372Tech', '0123456789') from dual -- would return 'Tech'

rtrim

select rtrim('tech ') from dual -- would return 'tech'
select rtrim('tech ', ' ') from dual -- would return 'tech'
select rtrim('123000', '0') from dual -- would return '123'
select rtrim('Tech123123', '123') from dual -- would return 'Tech'
select rtrim('123Tech123', '123') from dual -- would return '123Tech'
select rtrim('Techxyxzyyy', 'xyz') from dual -- would return 'Tech'
select rtrim('Tech6372', '0123456789') from dual -- would return 'Tech'


replace function replaces a sequence of characters in a string with another set of characters.

select replace('123123tech', '123') from dual -- would return 'tech'
select replace('123tech123', '123') from dual -- would return 'tech'
select replace('222tech', '2', '3') from dual -- would return '333tech'
select replace('0000123', '0') from dual -- would return '123'
select replace('0000123', '0', ' ') from dual -- would return ' 123'

translate function replaces a sequence of characters in a string with another set of characters. . However, it replaces a single character at a time

select translate('1tech23', '123', '456') from dual -- would return '4tech56'
select translate('222tech', '2ec', '3it') from dual -- would return '333tith'

substr

select substr('This is a test', 6, 2) from dual -- would return 'is'
select substr('This is a test', 6) from dual -- would return 'is a test'
select substr('TechOnTheNet', 1, 4) from dual -- would return 'Tech'
select substr('TechOnTheNet', -3, 3) from dual -- would return 'Net'
select substr('TechOnTheNet', -6, 3) from dual -- would return 'The'
select substr('TechOnTheNet', -8, 2) from dual -- would return 'On'


CASE

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;

select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;


TO_DSINTERVAL

SELECT SYSDATE, SYSDATE+ TO_DSINTERVAL('10 00:00:00') FROM DUAL