Single Row Character Functions take character/string Input and returns the result back as character or numeric output.
Dual Table
In these entire examples we will either be using a character data type column name or a character string. To use a character string we will use the DUAL table. It is a predefined system table with one row and one column. The column takes the heading as given in the expression followed by SELECT keyword. The row display the evaluation of the expression. The data of the Dual System Table is not saved. Here is an example to make you understand this
SELECT ‘Davis Stuart’ FROM DUAL; --Printing a string
‘DAVISSTUART’ |
Davis Stuart |
SELECT 4*5-1 FROM DUAL; -- displaying value of an expression after evaluation
4*5-1 |
19 |
SELECT 4*5-1 "final value" FROM DUAL; -- displaying value of an expression after evaluation and changing its display header
final value |
19 |
Dual table is most commonly used in ORACLE when user wants to evaluate an expression using inbuilt function without using a table.
Single Row Character Functions with examples
ASCII
ASCII function takes character data type as single input and returns its ASCII value in decimal. If the argument is not a single character the value returned represents the first character of the input.
Syntax
ASCII(Character String| column name with character data type)
Example
SELECT ASCII(‘a’) from DUAL;
ASCII(‘A’) |
97 |
SELECT SPName, ASCII(SPName) FROM SalesTbl;
SPNAME | ASCII(SPNAME) |
Bob Mathews | 66 |
Cathy Mills | 67 |
Alex Rode | 65 |
CHR
This function does the opposite of ASCII function. When it is called with ASCII value in decimal as argument, it returns the corresponding character.
Syntax
CHR(ASCII value in Decimal| column name with numeric data type)
Example
SELECT CHR(97) from DUAL;
CHR(97) |
a |
SELECT CHR(CustID), CustID,CustNAME FROM CustomerTbl;
CHR(CUSTID) | CUSTID | CUSTNAME |
e | 101 | Sid Vauly |
f | 102 | Stefan Mook |
g | 103 | Sujob Day |
CONCAT
CONCAT function is used to connect two strings or merge string data of two columns as one in the SELECT statement. The CONCAT function accepts two character strings as arguments and returns the joined string as output.
Syntax
CONCAT(CharSTring|column1 name with character data type, CharString2|column2 name with character data type)
The CONCAT function adds CharSTring2 or column2 data after CharSTring1 or column1 data. The combined string is displayed with header name CONCAT(CharSTring1| column1, CharString2| column2)
Example
SELECT CONCAT('My Name is ','Davis Stuart') from DUAL;
CONCAT(‘MYNAMEIS’,’DAVISSTUART’) |
My Name is Davis Stuart |
SELECT CONCAT(SPName,SPCity) FROM SalesTbl;
CONCAT(SPNAME,SPCITY) |
Bob MathewsNashville |
Cathy MillsSan Diego |
Alex Rode New York |
The joined strings will not have a blank space in between. If you need a blank space between two strings you can do it by nesting CONCAT functions like this
SELECT CONCAT(CONCAT(SPName,' '),SPCity) "Salerperson with City name" FROM SalesTbl;
Salerperson with City name |
Bob Mathews Nashville |
Cathy Mills San Diego |
Alex Rode New York |
INITCAP
The INITCAP function will produce the same string passed as arguments with first alphabets of each word in uppercase.
Syntax
INITCAP(CharString)
Example
SELECT INITCAP(‘my name is davis stuart’) from DUAL;
INITCAP(‘MYNAMEISDAVISSTUART’) |
My Name Is Davis Stuart |
SELECT INITCAP(SPNAME) FROM SalesTbl;
INITCAP(SPNAME) |
Bob Mathews |
Cathy Mills |
Alex Rode |
INSTR
The INSTR function displays the first position of an alphabet in a string passed as first argument. The output is in numeric.
Syntax
INSTR(CharString, Char)
This function returns the numeric position of first occurrence of Char in CharString. If Char does not exist in the string, the function returns 0. The position of characters in string start from 1. The index of last character In string is the length
Example
SELECT INSTR(‘my name is davis stuart’, ‘d’) from DUAL;
INSTR(‘MYNAMEISDAVISSTUART’,’D’) |
12 |
SELECT INSTR(SPNAME, 'l'), spname FROM SalesTbl;
INSTR(SPNAME,’L’) | SPNAME |
0 | Bob Mathews |
9 | Cathy Mills |
2 | Alex Rode |
The strings stored in a table are Case sensitive as shown in the example below. Here when the INSTR function is passed second argument as uppercase ‘L’ the function could not locate this character in the first argument.
SELECT INSTR(SPNAME, 'L'), spname FROM SalesTbl;
INSTR(SPNAME,’L’) | SPNAME |
0 | Bob Mathews |
0 | Cathy Mills |
0 | Alex Rode |
LENGTH
Length function takes single argument of character string type and returns the number of characters in it. The blank spaces in the string are also counted as part of the string.
Syntax
LENGTH(CharString)
Example
SELECT LENGTH(‘My Name is Davis Stuart’) from DUAL;
LENGTH(‘MYNAMEISDAVISSTUART’) |
23 |
SELECT LENGTH(SPNAME) FROM SalesTbl;
LENGTH(SPNAME) |
11 |
11 |
10 |
LOWER
LOWER Function accepts single string as argument. It display the string passed as argument in lowercase. It doesn’t change the case of the values stored in a table.
Syntax
LOWER(CharString)
Example
SELECT LOWER(‘My Name is Davis Stuart’) from DUAL;
LOWER(‘MYNAMEISDAVISSTUART’) |
my name is davis stuart |
SELECT LOWER(SPNAME) FROM SalesTbl;
LOWER(SPNAME) |
bob mathews |
cathy mills |
alex rode |
LPAD
LPAD means left pad. This function is used to pad a character or blank space on the left of a string passed as an argument and returns a string of length N.
Syntax
LPAD(CharString, N, Padchar)
The LPAD function returns a string of N length after adding Padchar on the Left of CharString. If CharString length is less than N then CharString is truncated to length N
Example
SELECT LPAD(‘1000’,8,’*’) from DUAL;
LPAD(‘1000′,8,’*’) |
****1000 |
SELECT LPAD(SPNAME, 20,’X’) FROM SalesTbl;
LPAD(SPNAME,20,’X’) |
XXXXXXXXXBob Mathews |
XXXXXXXXXCathy Mills |
XXXXXXXXXXAlex Rode |
LTRIM
This is the trim function used to truncate specific character on the left of the string. In other words it means that you can delete any characters at the beginning of the string passed as first argument.
Syntax
LTRIM(CharString [,Trimchar])
The LTRIM function returns a string after deleting the Trimchar at the beginning of CharString. If Trimchar is not available at the beginning, the function returns CharString without any change. Trimchar is optional in this case. If it is not provided the LTRIM function will remove all the leading blank spaces of the CharString
Example
SELECT LTRIM(‘ David Beckham’) from DUAL;
LTRIM(‘DAVIDBECKHAM’) |
David Beckham |
SELECT LTRIM(‘ David Beckham’,’ ‘) from DUAL;
LTRIM(‘DAVIDBECKHAM’,”) |
David Beckham |
SELECT LTRIM(‘David Beckham’,’Da‘) from DUAL;
LTRIM(‘DAVIDBECKHAM’,’DA’) |
vid Beckham |
SELECT LTRIM(SPNAME, ‘ ‘) FROM SalesTbl;
LTRIM(SPNAME,’A’) | SPNAME |
Bob Mathews | Bob Mathews |
Cathy Mills | Cathy Mills |
lex Rode | Alex Rode |
REPLACE
Replace function is used when you wish to replace certain characters in one string with another set of characters.
Syntax
REPLACE(CharString, FindChar, ReplChar)
The REPLACE function returns a string after replacing all occurrences of FindChar with ReplChar in the CharString. If FindChar is NULL then CharString is returned with no changes. If ReplChar is null then all occurances of FindChar are removed from the CharString
Example
SELECT REPLACE(‘Ba Ba Black Sheep’,’B’, ‘S’) from DUAL;
REPLACE(‘BABABLACKSHEEP’,’B’,’S’) |
Sa Sa Slack Sheep |
SELECT REPLACE(‘Five Little Monkeys Jumping on the Bed’,’ Jumping’, ‘Sleeping’) from DUAL;
REPLACE(‘FIVELITTLEMONKEYSJUMPINGONTHEBED’,’JUMPING’,’SLEEPING’) |
Five Little Monkeys Sleeping on the Bed |
SELECT REPLACE(SPNAME, ‘l’,’o’) FROM SalesTbl;
SPNAME | REPLACE(SPNAME,’L’,’O’) |
Bob Mathews | Bob Mathews |
Cathy Mills | Cathy Mioos |
Alex Rode | Aoex Rode |
RPAD
RPAD means Right pad. This function is used to pad a character or blank space on the right of a string passed as an argument and returns a string of length N.
Syntax
RPAD(CharString, N, Padchar)
The RPAD function returns a string of N length after adding Padchar on the right of CharString. If CharString length is less than N then CharString is truncated to length N
Example
SELECT RPAD(‘1000’,8,’*’) from DUAL;
RPAD(‘1000′,8,’*’) |
1000**** |
SELECT RPAD(SPNAME, 20,’X’) FROM SalesTbl;
RPAD(SPNAME,20,’X’) |
Bob MathewsXXXXXXXXX |
Cathy MillsXXXXXXXXX |
Alex Rode XXXXXXXXXX |
RTRIM
This is the trim function used to truncate specific characters on the right of the string. In other words it means that you can delete any characters at the end of the string passed as first argument.
Syntax
RTRIM(CharString [,Trimchar])
The RTRIM function returns a string after deleting the Trimchar at the end of CharString. If Trimchar is not available at the end the function returns CharString without any change. Trimchar is optional in this case. If it is not provided the RTRIM function will remove all the trailing blank spaces of the CharString
Example
SELECT LENGTH('David Beckham '), LENGTH(RTRIM('David Beckham ')) from DUAL;
LENGTH(‘DAVIDBECKHAM’) | LENGTH(RTRIM(‘DAVIDBECKHAM’)) |
17 | 13 |
SELECT RTRIM(‘David Beckham ’,’ham ‘) from DUAL;
RTRIM(‘DAVIDBECKHAM’,’HAM’) |
David Beck |
SELECT RTRIM('David Beckham','jil') from DUAL;
RTRIM(‘DAVIDBECKHAM’,’JIL’) |
David Beckham |
SELECT SPNAME, RTRIM(SPNAME, ‘ws ‘) FROM SalesTbl;
SPNAME | RTRIM(SPNAME,’WS’) |
Bob Mathews | Bob Mathe |
Cathy Mills | Cathy Mill |
Alex Rode | Alex Rode |
SUBSTR
It is called the Sub String Function. It is used when you need to extract a few characters from a string. Here you need to pass 3 arguments. First is the string, second start position from where you want to extract substring and third the required length of the substring.
Syntax
SUBSTR(CharString, StartPos, NumOfChars)
The SUBSTR function returns a string after extracting NumOfChars starting from StartPos in the CharString. If NumOfChars is NULL then the string is extracted starting from StartPos till the end of the string. If StartPos is negative then the extraction of a substring takes place from right to left
Example
SELECT SUBSTR(‘Ba Ba Black Sheep’,6, 5) from DUAL;
SUBSTR(‘BABABLACKSHEEP’,6,5) |
Blac |
SELECT SUBSTR(‘Ba Ba Black Sheep’,6) from DUAL;
SUBSTR(‘BABABLACKSHEEP’,6) |
Black Sheep |
SELECT SUBSTR(SPNAME, 3,4) FROM SalesTbl;
SUBSTR(SPNAME,3,4) |
b Ma |
thy |
ex R |
SELECT SUBSTR(SPNAME, 3) FROM SalesTbl;
SUBSTR(SPNAME,3) |
b Mathews |
thy Mills |
ex Rode |
TRANSLATE
This function is used to substitute characters In one string with other set of characters. This is a function that uses positional characters for replacement. Suppose the characters to be replaced are ‘abc’ and characters replacing them are ‘xyz’. Every occurrence of ‘a ‘ is replaced by x , every occurrence of ‘b’ is replaced by ‘y’ and every occurrence of ‘c’ is replaced by ‘z’.
Syntax
TRANSLATE(CharString, SourceChars, TargetChars)
The TRANSLATE function returns a string after replacing each character defined in SourceChars argument with corresponding character in TargetChars. If characters in SourceChars are less than characters in TargetChars then the respective characters are ignored in CharString. If characters in SourceChars are more than characters in TargetChars then the respective characters are removed in CharString.
Example
SELECT TRANSLATE (‘Ba Ba Black Sheep’,’BS’,’ST’) from DUAL;--all B’s are replaced by S and all S ‘s by T
TRANSLATE(‘BABABLACKSHEEP’,’BS’,’ST’) |
Sa Sa Slack Theep |
SELECT TRANSLATE (SPNAME, ‘abc’,’xyz’) FROM SalesTbl;
SPNAME | TRANSLATE(SPNAME,’ABC’,’XYZ’) |
Bob Mathews | Boy Mxthews |
Cathy Mills | Cxthy Mills |
Alex Rode | Alex Rode |
TRIM
This is the trim function used to truncate specific character on both sided of the string. In other words it means that you can delete any characters at the end and beginning of the string passed as first argument.
Syntax
TRIM( [ [ LEADING | TRAILING | BOTH ] TrimChar FROM ] CharString )
The TRIM function returns a string after deleting the Trimchar at both ends of the CharString if Leading Trailing or Both keywords are not specified in argument. If Trimchar is not available at the beginning or end of the CharString the function returns CharString without any change. If it is not provided the TRIM function will remove all the preceding and trailing blank spaces of the CharString
Example
SELECT TRIM(' csveda ') from DUAL;
TRIM(‘CSVEDA’) |
csveda |
SELECT TRIM(' ' FROM ' csveda ') from DUAL;
TRIM(”FROM’CSVEDA’) |
csveda |
SELECT TRIM(LEADING 'c' FROM 'csveda') from DUAL;
TRIM(LEADING’C’FROM’CSVEDA’) |
sveda |
SELECT TRIM(TRAILING 'a' FROM 'csveda') from DUAL;
TRIM(TRAILING’A’FROM’CSVEDA’) |
csved |
SELECT TRIM(BOTH '*' FROM '**csveda**') from DUAL;
TRIM(BOTH’*’FROM’**CSVEDA**’) |
csveda |
SELECT TRIM(SPNAME) FROM SalesTbl;
TRIM(SPNAME) |
Bob Mathews |
Cathy Mills |
Alex Rode |
UPPER
UPPER Function accepts single string as argument. It display the string passed as argument in uppercase. It doesn’t change the case of the values stored in a table.
Syntax
UPPER(CharString)
Example
SELECT UPPER(‘My Name is Davis Stuart’) from DUAL;
UPPER(‘MYNAMEISDAVISSTUART’) |
MY NAME IS DAVIS STUART |
SELECT UPPER(SPNAME) FROM SalesTbl;
UPPER(SPNAME) |
BOB MATHEWS |
CATHY MILLS |
ALEX RODE |
Points to remember
- You can nest these functions within each other
- These functions with SELECT statement change only the data to be displayed to user. No change occurs in the stored data.