Oracle String functions

ASCII( p_character )

COALESCE( p_expression1, p_expression2, ... p_expressionN )

-- --> 'Abc'
Select COALESCE(null, 'Abc', '123') as Column1 from dual;


-- --> 'Aaa'
Select COALESCE('Aaa', null, '345') as Column1 from dual;


-- --> 'Sss'
Select COALESCE(null, null, null, 'Sss') as Column1 from dual;

UPPER( p_string )

-- --> TOM CAT
Select Upper('Tom Cat') From Dual;

-- --> TOM CAT
Select Upper('tom cat') From Dual;

LOWER( p_string )

-- --> tom cat
Select Lower('Tom Cat') From Dual;

-- --> tom cat
Select Lower('tom cat') From Dual;

INITCAP(  p_string )

-- --> Tom Cat
Select Initcap('TOM CAT') From Dual;

-- --> Tom Cat
Select Initcap('tom cat') From Dual;

CHR( p_number )

CHR( p_number  using nchar_cs)

-- --> @
Select  chr(64) From dual;

-- --> A
Select  chr(65) From dual;

-- --> â
Select  chr(50082) From dual;

-- --> 쎢
Select  chr(50082  using nchar_cs) From dual;

CONCAT( p_string1, p_string2 )

-- --> Tom Cat
Select Concat('Tom ', 'Cat') From dual;

CONCAT( p_string1, p_string2 )

Declare
   C1 Clob := To_Clob('Tom ');
   C2 Clob := To_Clob('Cat');
   C3 Clob;
   C4 Clob;
Begin
   Select Concat(C1
                ,C2)
   Into   C3
   From   Dual;
   ----
   -- --> Tom Cat
   Dbms_Output.Put_Line(C3);
   ----
   C4 := Concat(C1
               ,C2);
   -- --> Tom Cat
   Dbms_Output.Put_Line(C4);
End;

INSTR( p_string, p_substring [, p_start_position [,  p_occurrence ] ] )

-- --> 14
Select Instr('Corporate Floor', 'or', 5, 2) as Column1 from dual;


-- --> 5
Select Instr('Corporate Floor', 'or', -10) as Column1 from dual;

INSTRB (p_string , p_substring [, p_start_position [, p_occurrence]])

-- --> 6
Select Instrb('Tiếng Việt', 'n') from dual;

-- --> 11
Select Instrb('Tiếng Việt', 'ệ' ,5) from dual;

LENGTH( p_string )

-- --> 7
Select Length('Tom Cat') As Column1 From Dual;

LENGTHB( p_string )

-- Null - Null
Select Lengthb(Null) Column1
      ,Lengthb('') Column2
From   Dual;

-- 7
Select Lengthb('Tom Cat') Column1 From dual;

-- 2
Select Lengthb('Â') As Column1 From Dual;

-- length of characters
-- --> 10
Select Length('Tiếng Việt') from dual;

-- length of bytes
-- --> 14
Select Lengthb('Tiếng Việt') from dual;

LPAD( p_string, p_padded_length [, p_pad_string] )

-- 00123
Select Lpad('123', 5, '0')  From   Dual;

-- 12345
Select Lpad('1234567', 5, '0') From Dual;


-- '       Tom'
Select Lpad('Tom', 10) From   Dual;

-- 'To'
Select Lpad('Tom', 2) From   Dual;

LTRIM( p_string [, p_trim_string] )

-- --> TOM
Select LTRIM('   TOM') From dual;

-- --> 789
Select LTRIM('000789', '0') From Dual;


-- --> ABC01
Select LTRIM('0101ABC01', '01') From dual;


-- --> 4210ABC
Select LTRIM('34210ABC', '0123') From dual;

RTRIM( p_string [, trim_string ] )

-- --> TOM
Select RTRIM('TOM   ') From dual;

-- --> 000789
Select RTRIM('00078900', '0') From Dual;


-- --> 0101ABC
Select RTRIM('0101ABC0101', '01') From dual;


-- --> ABC34
Select RTRIM('ABC34210', '0123') From dual;

TRIM( [ [ LEADING | TRAILING | BOTH ] p_trim_character FROM ] p_string )

-- --> TOM
Select TRIM('  TOM   ') From dual;


-- --> 78900
Select TRIM(Leading '0' from '00078900') From Dual;


-- --> 001ABC
Select TRIM(Trailing '0' from '001ABC00') From dual;


-- --> ABC21
Select TRIM(Both '0' from '00ABC21000' ) From dual;

SUBSTR( p_string, p_start_position [, p_length ] )

-- 'is is'
Select substr('this is a text message',3,5) Column1 from dual;

-- 'is is a text message'
Select substr('this is a text message',3) Column1 from dual;

-- 'messa'
Select substr('this is a text message',-7,5) Column1 from dual;

-- 'message'
Select substr('this is a text message',-7) Column1 from dual;

-- 'age'
Select substr('this is a text message',-3, 5) Column1 from dual;

-- null
Select substr('this is a text message',-3, -5) Column1 from dual;

SUBSTRB( p_string, p_start_position [, p_length ] )

-- ây là
Select Substrb('Đây là Tiếng Việt', 3, 7) from dual;


-- y là Ti
Select Substr('Đây là Tiếng Việt', 3, 7) from dual;