Home > SQL, Technical > Character Manipulation functions in Oracle SQL

Character Manipulation functions in Oracle SQL

July 26, 2012

Function 1: UPPER

Purpose :  Returns the string in uppercase

Syntax : UPPER(‘str’)

Example : SELECT UPPER(‘karuvachi’) from Dual;

Output:KARUVACHI

———————————————————————————————-

Function 2: lower

Purpose :  Returns the string in lowercase

Syntax : lower(‘str’)

Example : SELECT LOWER(‘KaRuVaChi’) FROM DUAL;

Output:karuvachi

———————————————————————————————-

Function 3: Initcap

Purpose :  Returns the string with first letter in uppercase and rest of the letters in lowercase

Syntax : Initcap(‘str’)

Example : SELECT Initcap(‘KaRuVaChi’) FROM DUAL;

Output:Karuvachi

———————————————————————————————-

Function 4: Concat

Purpose :  Concatenate two strings

Syntax : concat(‘str1′,’str2’)

Example : SELECT CONCAT(‘Karu’,’Nand’) FROM DUAL;

Output:KaruNand

———————————————————————————————-

Function 5: Lpad

Purpose :  Pad in the left side of the string for given times – length of the string

Syntax : Lpad(‘str1′,n,’str2’)

Example : SELECT Lpad(‘Karu’,6,’?’)  FROM DUAL;

Output:??Karu

———————————————————————————————-

Function 6: Rpad

Purpose :  Pad in the right side of the string for given times – length of the string

Syntax : Rpad(‘str1′,n,’str2’)

Example : SELECT Rpad(‘Karu’,6,’?’)  FROM DUAL;

Output:Karu??

———————————————————————————————-

Function 7: trim

Purpose :  Trim the whitespaces in both the sides of the string

Syntax : trim(‘str’)

Example : SELECT TRIM(‘    karu      ‘)  FROM DUAL;

Output:karu

———————————————————————————————-

Function 8: Ltrim

Purpose :  Trim the whitespaces in left the side of the string

Syntax : Ltrim(‘str’)

Example : SELECT LTRIM(‘    karu      ‘)  FROM DUAL;

Output:karu….(. dot are spaces)

———————————————————————————————-

Function 9: Rtrim

Purpose :  Trim the whitespaces in right the side of the string

Syntax : Rtrim(‘str’)

Example : SELECT RTRIM(‘    karu      ‘)  FROM DUAL;

Output:….karu(. dot are spaces)

———————————————————————————————-

Function 10: Length

Purpose :  length of the string

Syntax : length(‘str’)

Example : SELECT LENGTH(‘karuvachi’)  FROM DUAL;

Output:9

———————————————————————————————-

Function 11: Instr

Purpose :  Find the position of the string in another string

Syntax : Instr(‘str1′,’str2’)

Example : SELECT INSTR(‘karuvachi’,’ka’)  FROM DUAL;

Output:1

———————————————————————————————-

Function 12: substr

Purpose :  get a sub string from string

Syntax : substr(‘str’,start_pos,number_of_chars)

Example : SELECT substr(‘karuvachi’,2,4)  FROM DUAL;

Output:aruv

———————————————————————————————-

Function 13: SOUNDEX

Purpose :  translates a source string into its soundex code.

Syntax : SOUNDEX(‘str’)

Example : SELECT SOUNDEX(‘karu’)  FROM DUAL;

Output:K600

Advertisements
Categories: SQL, Technical
%d bloggers like this: