Character Functions

Common SQL queries

 

Purpose	SQL Query	O/p	Remarks
Database Name	select ora_database_name from dual;	SQLDB	Return the database name
Full Information of DB	select * from v$database;	DbName,creationdate ..	Full information about the database
Current Time	select SYSDATE from dual;	03-JUN-14
Single line comment	-- description
Multiline comments	/* …..  ……  */

 Character Functions:

 

Function	SQL Query
Length – No of characters in a string	Returns the Length (no of charaters ) of 'MALAYALAM' – 9   select LENGTH('MALAYALAM') from dual; 9   select LENGTH(ACCT_NAME) from ACCOUNTS where ACCT_ID='598934';
INITCAP  – First letterer of each words to capital letter	select INITCAP('hello to all') from dual; Hello To All   select INITCAP (ACCT_NAME) from ACCOUNTS where ACCT_ID='598934'
CONCAT  – Concatenate 2 values	select CONCAT('EmP No:','18236') from dual;   EmP No:18236     select CONCAT(ACCT_ID || ':',  ACCT_NAME)  from ACCOUNTS where ACCT_ID = '8734545';
|| ( connate operator) Concatenate 2 values	select 'EmP No:' ||'18236'  EMPDetails rom dual;   EmP No:18236
UPPER -Convert characters to upper case	select UPPER('MALAYALAM') from dual;   MALAYALAM                    select * from  ACCOUNTS where UPPER(acct_name) like '%VISHNU%'
LOWER -Convert characters to lower case	select LOWER('MALAYALAM') from dual;   malayalam     select * from ACCOUNTS where LOWER(acct_name) like '%vishnu%'
ASCII -Convert characters to ASCII value	select ASCII('0') from dual;   48
INSTR -To find the position of the given letter   INSTR(Value, character to find, starting position from where we need to find Chara position, which occurrence position you need to find)	select INSTR('MALAYALAM','L')  from dual;   3 select INSTR('MALAYALAM','A')  from dual;   2 select INSTR('MALAYALAM','A',1)  from dual 2 select INSTR('MALAYALAM','A',1,2)  from dual;   4 select INSTR('MALAYALAM','A',3,2)  from dual;   6   select INSTR(acct_name,'I',8, 2)  from accounts  where acct_id = '124466'
LTRIM LTRIM(String,Value)   -Trim the given Value from the left side of the String	select LTRIM('aaabcd','a') from dual;   bcd   select LTRIM('   MALAYALAM  ',' ') from dual;   MALAYALAM'
RTRIM RTRIM(String,Value)   -Trim the given Value from the right  side of the String	select RTRIM('aaabcdddd','d') from dual;   Aaabc select RTRIM(' MALAYALAM    ',' ') from dual;   ‘MALAYALAM'
REPLACE   REPLACE(String,valuetofind,valuetoreplace)   -Find the valuetofind in the String and replace with valuetoreplace	select REPLACE('aaabcdddd','a','e') from dual;   eeebcdddd   select REPLACE('aaabcdddd','aa','e') from dual;   eabcdddd
SUBSTR   SUBSTR(String,startingposition,endingposition) -substring will extract the String from startingposition to endingposition and return that extracted String	select SUBSTR('aaabcdddd',4,10) from dual;   bcdddd
TRANSLATE   TRANSLATE(String, series of characters to find, series of characters to replace)	select TRANSLATE('aaabcdddd','abd','efg') from dual;     eeefcgggg   In the  above example series of characters to find - 'abd'                                 series of characters to replace - 'efg' So it will find every ‘a’ from String 'aaabcdddd' and replace it with ‘e’ Next, ‘b’ with ‘f’ next – ‘d’ with ‘g’