Character Functions:
Single row Character functions accept character data as input and can return both
character and numeric Values.
Character Functions are divided into the following:
1 Case manipulation functions
2 Character manipulation functions
Case manipulation functions:
LOWER
UPPER
INITCAP
Character manipulation functions:
CONCAT
SUBSTR
LENGTH
INSTR
LPAD|RPAD
TRIM
REPLACE
ASCII:
The Oracle/PLSQL ASCII function returns the NUMBER code that represents the specified
character.
Syntax:
ASCII( single_character )
Returns
The ASCII function returns a numeric value.
Examples:
ASCII('t')
Result: 116
ASCII('T')
Result: 84
ASCII('T2')
Result: 84
*******************************************************************************************
CHAR:
The Oracle/PLSQL CHR function is the opposite of the ASCII function. It returns the
character based on the NUMBER code.
Syntax:
CHR( number_code )
Returns:
The CHR function returns a string value.
Examples:
CHR(116)
Result: 't'
CHR(84)
Result: 'T'
*******************************************************************************************
CONCAT:
The Oracle/PLSQL CONCAT function allows you to concatenate two strings together.
Syntax:
CONCAT( string1, string2 )
Returns
The CONCAT function returns a string value.
Examples:
CONCAT('Tech on', ' the Net')
Result: 'Tech on the Net'
CONCAT('a', 'b')
INITCAP:
The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and
the rest to lowercase.
Syntax:
INITCAP( string1 )
Result: 'ab'
Returns
The INITCAP function returns a string value.
Examples:
INITCAP('tech on the net');
Result: 'Tech On The Net'
INITCAP('GEORGE BURNS');
Result: 'George Burns'
*******************************************************************************************
INSTR:
The Oracle/PLSQL INSTR function returns the location of a substring in a string.
Syntax:
INSTR( string, substring [, start_position [, th_appearance ] ] )
Returns
The INSTR function returns a numeric value. The first position in the string is 1.
If substring is not found in string, then the INSTR function will return 0.
Examples:
INSTR('Tech on the net', 'e')
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 2)
Result: 11 (the second occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 3)
Result: 14 (the third occurrence of 'e')
INSTR('Tech on the net', 'e', -3, 2)
Result: 2
*******************************************************************************************
LENGTH:
The Oracle/PLSQL LENGTH function returns the length of the specified string.
Syntax:
LENGTH( string1 )
Returns
The LENGTH function returns a numeric value.
If string1 is NULL, then the LENGTH function will return NULL.
Examples:
LENGTH(NULL)
Result: NULL
LENGTH('')
Result: NULL
LENGTH(' ')
Result: 1
LENGTH('Tech on the Net')
Result: 15
LENGTH('Tech on the Net ')
Result: 16
LOWER:
The Oracle/PLSQL LOWER function converts all letters in the specified string to lowercase.
If there are characters in the string that are not letters, they are unaffected by this function.
Syntax:
LOWER( string1 )
Returns
The LOWER function returns a string value.
Examples:
LOWER('Tech on the Net');
Result: 'tech on the net'
LOWER('GEORGE BURNS 123 ');
Result: 'george burns 123 '
*******************************************************************************************
UPPER:
The Oracle/PLSQL UPPER function converts all letters in the specified string to uppercase.
If there are characters in the string that are not letters, they are unaffected by this function.
Syntax:
UPPER( string1 )
Returns
The UPPER function returns a string value.
Examples:
UPPER('Tech on the Net')
Result: 'TECH ON THE NET'
UPPER('george burns 123 ')
Result: 'GEORGE BURNS 123
SUBSTR:
The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.
Syntax:
SUBSTR( string, start_position [, length ] )
Returns:
The SUBSTR function returns a string value.
If length is a negative number, then the SUBSTR function will return a NULL value.
Examples:
SUBSTR('This is a test', 6, 2)
Result: 'is'
SUBSTR('This is a test', 6)
Result: 'is a test'
SUBSTR('TechOnTheNet', 1, 4)
Result: 'Tech'
SUBSTR('TechOnTheNet', -3, 3)
Result: 'Net'
SUBSTR('TechOnTheNet', -6, 3)
Result: 'The'
SUBSTR('TechOnTheNet', -8, 2)
Result: 'On'
*******************************************************************************************
TRIM:
The Oracle/PLSQL TRIM function removes all specified characters either from the
beginning or the end of a string.
Syntax:
TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )
Returns:
The TRIM function returns a string value.
Examples:
TRIM(' tech ')
Result: 'tech'
TRIM(' ' FROM ' tech ')
Result: 'tech'
TRIM(LEADING '0' FROM '000123')
Result: '123'
TRIM(TRAILING '1' FROM 'Tech1')
Result: 'Tech'
TRIM(BOTH '1' FROM '123Tech111')
Result: '23Tech'
*******************************************************************************************
REPLACE:
The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with
another set of characters.
Syntax:
REPLACE( string1, string_to_replace [, replacement_string] )
Returns:
The REPLACE function returns a string value.
Examples:
REPLACE('123123tech', '123');
Result: 'tech'
REPLACE('123tech123', '123');
Result:'tech'
REPLACE('222tech', '2', '3');
Result: '333tech'
REPLACE('0000123', '0');
Result: '123'
REPLACE('0000123', '0', ' ');
Result: ' 123'
*******************************************************************************************
TRANSLATE:
The Oracle/PLSQL TRANSLATE function replaces a sequence of characters in a
string with another set of characters.
However, it replaces a single character at a time.
For example, 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, and so on.
Syntax:
TRANSLATE( string1, string_to_replace, replacement_string )
Returns:
The TRANSLATE function returns a string value.
Examples:
TRANSLATE('1tech23', '123', '456')
Result: '4tech56'
TRANSLATE('222tech', '2ec', '3it')
Result: '333tith'
*******************************************************************************************
RPAD:
The Oracle/PLSQL RPAD function pads the right-side of a string with a specific set of
characters (when string1 is not null).
Syntax:
RPAD( string1, padded_length [, pad_string] )
Returns:
The RPAD function returns a string value.
Examples:
RPAD('tech', 7)
Result: 'tech '
RPAD('tech', 2)
Result: 'te'
RPAD('tech', 8, '0')
Result: 'tech0000'
RPAD('tech on the net', 15, 'z')
Result: 'tech on the net'
RPAD('tech on the net', 16, 'z')
Result: 'tech on the netz'
*******************************************************************************************
LPAD
The Oracle/PLSQL LPAD function pads the left-side of a string with a specific set of
characters (when string1 is not null).
Syntax:
LPAD( string1, padded_length [, pad_string] )
Returns:
The LPAD function returns a string value.
Examples:
LPAD('tech', 7);
Result: ' tech'
LPAD('tech', 2);
Result: 'te'
LPAD('tech', 8, '0');
Result: '0000tech'
LPAD('tech on the net', 15, 'z');
Result: 'tech on the net'
LPAD('tech on the net', 16, 'z');
Result: 'ztech on the net'