CHARACTER Functions:
LOWER(string) - Returns string in lower case
-
SELECT LOWER('AAAA') FROM dual -- returns 'aaaa'
-
UPPER(string) - Returns string in UPPER CASE
-
SELECT UPPER('aaaa') FROM dual -- returns 'AAAA'
-
INITCAP(string) - Returns string with first character in UPPER CASE and other characters in lower case
-
SELECT INITCAP('aaaa') FROM dual -- returns 'Aaaa'
-
SUBSTR(string,m,[n]) - Returns n no of characters from position m for string (n is optional)
-
SELECT SUBSTR('Welcome to India',1,3) from dual -- returns 'Wel'
-
INSTR(string,c) - Returns the first position of character c from string. INSTR is case sensitive
-
SELECT INSTR('Well Done', 'D') FROM dual -- returns 6
-
SELECT INSTR('Well Done', 'Well') FROM dual -- returns 1 (Here the string 'Well' matches with the part of string 'Well Done', so it will return the position of the first character ie 'W' here)
-
SELECT INSTR('Well Done' ,'d') FROM dual -- returns 0
-
LENGTH(string) - Returns length of string
-
SELECT LENGTH('aaaa') FROM dual -- returns 4
-
RPAD(string,m,[c]) - Returns string of length m right padded with character c. If c is not specified, then the string will be right padded with SPACE
-
SELECT RPAD('aaaa',6,'*') FROM dual -- returns 'aaaa**'
-
SELECT RPAD('aaaa',2,'*') FROM dual -- returns 'aa'
-
SELECT RPAD('aaaa',12) FROM dual -- returns 'aaaa '
-
LPAD(string,m,[c]) - Returns string of length m left padded with character c. If c is not specified, then the string will be left padded with SPACE.
-
SELECT RPAD('aaaa',6,'*') FROM dual -- returns '**aaaa'
-
SELECT RPAD('aaaa',2,'*') FROM dual -- returns 'aa'
-
SELECT RPAD('aaaa',12) FROM dual -- returns ' aaaa'
-
CONCAT(string1,string2) - Returns concatenated string string1string2
-
SELECT CONCAT('Well','Done') FROM dual -- returns 'WellDone'
-
CHR(m) - Returns character value of number m
-
SELECT CHR(65) FROM dual -- returns 'A'
-
ASCII(c) - Returns ASCII number of character c
[code=oracle]
SELECT ASCII('A') FROM dual -- returns 65
SELECT ASCII('ABCD') FROM dual -- returns 65 (ASCII value of first character only)
TRIM(string) - Returns string after trimming spaces on both the sides of the string
-
SELECT TRIM(' sfsdfsfs ') FROM dual -- returns 'sfsdfsfs'
-
LTRIM(string) - Returns string after trimming spaces on left side of the string
-
SELECT TRIM(' sfsdfsfs ') FROM dual -- returns 'sfsdfsfs '
-
RTRIM(string) - Returns string after trimming spaces on right side of the string
-
SELECT TRIM(' sfsdfsfs ') FROM dual -- returns ' sfsdfsfs'
-
REPLACE(string,m,[c]) - Returns string after replacing all the occurances of character m with character c. If c is not specified, then the character m is truncated from the string
-
SELECT REPLACE('Welcomes','s','d') FROM dual -- returns 'Welcomed'
-
SELECT REPLACE('ab','a','ab') FROM dual -- returns 'abb'
-
SELECT REPLACE('ab','ac','ab') FROM dual -- returns 'ab' (Here no replacement takes place because there is no match between string and m. Here string = 'ab' and m= 'ac')
-
TRANSLATE(string,m,c) - Returns string after replacing all the occurances of character m with character c. Here c is mandatory. If m is more than one character, then the occurance of any character of m in string will be replaced with character c
-
SELECT TRANSLATE('AA','Aa','bc') FROM dual -- returns 'bb' (Here 'A' is replaced by 'b' in string 'AA')
-
SELECT TRANSLATE('Aa','Aa','bcfsfffssf') FROM dual -- returns 'bc' (Here 'Aa' is replaced with first two characters 'bc')
-
