SQL- Single Row Character Functions in Oracle

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.