SQL :- String Function

--====================================================

SQL :- String Function

--====================================================

--ASCII   Returns an ASCII code value of a character.

select ASCII('A'), ASCII('a'), ASCII('!') from DUAL

Result :-

ASCII('A')	ASCII('A')_1	ASCII('!')
65	97	33

---------------------------------

--CHR  Converts a numeric value to its corresponding ASCII character.

select  CHR('65') ,  CHR('97'),  CHR('33') from DUAL

Result :-

CHR('65')	CHR('97')	CHR('33')
A	a	!

---------------------------------

--CONCAT   Concatenate two strings and return the combined string.

select  CONCAT('Oracle *','* Shooter') , CONCAT('10','2')  from DUAL

Result :-

CONCAT('ORACLE*','*SHOOTER')	CONCAT('10','2')
Oracle ** Shooter	102

---------------------------------

--DUMP   Return a string value (VARCHAR2) that includes the datatype code, length measured in bytes, and internal representation of a specified expression.

96:- STRING

2:- NUMBER

13 DATE

select  DUMP('100'), LENGTH ('100'), ASCII('1'), ASCII('0') from DUAL

Result :-

DUMP('100')	LENGTH('100')	ASCII('1')	ASCII('0')
Typ=96 Len=3: 49,48,48	3	49	48

---------------------------------

-- INSTR  Search for a substring and return the location of the substring in a string

select  INSTR( 'This is a audio playlist', 'is', 1, 3),   INSTR( 'This is a audio playlist', 'is', -1, 1)  from DUAL 

Result :-

INSTR('THISISAAUDIOPLAYLIST','IS',1,3)	INSTR('THISISAAUDIOPLAYLIST','IS',-1,1)
22	22

---------------------------------

--SUBSTR   Extract a substring from a string.

select SUBSTR('Oracle Shooter',1, 6 ) from DUAL

Result :-

SUBSTR('ORACLESHOOTER',1,6)

Oracle

---------------------------------

-- LENGTH Return the number of characters (or length) of a specified string

select  LENGTH('Oracle Shooter') from DUAL

Result :- 14

---------------------------------

--LOWER    Return a string with all characters converted to lowercase.

select LOWER('Oracle SHOOTER')  from DUAL

Result :-  oracle shooter

---------------------------------

--UPPER   Convert all characters in a specified string to uppercase.

select UPPER('Oracle Shooter') from DUAL

Result :- ORACLE SHOOTER

---------------------------------

-- INITCAP Converts the first character in each word in a specified string to uppercase and the rest to lowercase.

select  INITCAP('oracle shooter')  from DUAL

Result :- Oracle Shooter

---------------------------------

--LPAD    Return a string that is left-padded with the specified characters to a certain length.

select  LPAD('123456789',12,'0') from DUAL

Result :-  000123456789

---------------------------------

--RPAD  Return a string that is right-padded with the specified characters to a certain length.

select  RPAD('123456789',12,' ') from DUAL

Result :- 123456789  

---------------------------------

--LTRIM  Remove spaces or other specified characters in a set from the left end of a string.

select  LTRIM('    Oracle Shooter     ') from DUAL

Result :- ‘Oracle Shooter     ‘

---------------------------------

--RTRIM   Remove all spaces or specified character in a set from the right end of a string.

select RTRIM('      Oracle Shooter      ') from DUAL

Result :- ‘      Oracle Shooter’

---------------------------------

--TRIM    Remove all spaces or specified character in a set from the right end of a string.

select TRIM('    Oracle     Shooter    ') from DUAL

Result :- ‘Oracle Shooter’

---------------------------------

--TRANSLATE  Replace all occurrences of characters by other characters in a string.

select TRANSLATE('Ram is a Good Boy','Good','1234') from DUAL

Result :- Ram is a 1224 B2y

---------------------------------

--REPLACE    Replace all occurrences of a substring by another substring in a string.

select REPLACE('Ram is a Good Boy','Good','007') from DUAL

Result :- Ram is a 007 Boy