Google Search

Wednesday, October 12, 2011

Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round, to_char, to_date) DBMS M.TECH LAB MANUAL


Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round, to_char, to_date)

1. Conversion functions:
To_char: TO_CHAR (number) converts n to a value of VARCHAR2 data type, using the optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.

SQL>select to_char(65,'RN')from dual;

LXV

To_number : TO_NUMBER converts expr to a value of NUMBER data type.
SQL> Select to_number('1234.64') from Dual;
1234.64

To_date: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.
SQL>SELECT TO_DATE('January 15, 1989, 11:00 A.M.')FROM DUAL;
 
TO_DATE('
---------
15-JAN-89

2. String functions:
Concat: CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes 
SQL>SELECT CONCAT(‘ORACLE’,’CORPORATION’)FROM DUAL;
ORACLECORPORATION

Lpad: LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2.
SQL>SELECT LPAD(‘ORACLE’,15,’*’)FROM DUAL;
*********ORACLE


Rpad: RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary. 
SQL>SELECT RPAD (‘ORACLE’,15,’*’)FROM DUAL;
ORACLE*********

Ltrim: Returns a character expression after removing leading blanks.
SQL>SELECT LTRIM(‘SSMITHSS’,’S’)FROM DUAL;
MITHSS

Rtrim: Returns a character string after truncating all trailing blanks
SQL>SELECT RTRIM(‘SSMITHSS’,’S’)FROM DUAL;
SSMITH

Lower: Returns a character expression after converting uppercase character data to lowercase.
SQL>SELECT LOWER(‘DBMS’)FROM DUAL;
dbms

Upper: Returns a character expression with lowercase character data converted to uppercase
SQL>SELECT UPPER(‘dbms’)FROM DUAL;
DBMS
Length: Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
SQL>SELECT LENGTH(‘DATABASE’)FROM DUAL;
8
Substr: Returns part of a character, binary, text, or image expression.
SQL>SELECT SUBSTR(‘ABCDEFGHIJ’3,4)FROM DUAL;
CDEF

Instr: The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence.
SQL>SELECT INSTR('CORPORATE FLOOR','OR',3,2)FROM DUAL;
14


3. Date functions:
Sysdate:
SQL>SELECT SYSDATE FROM DUAL;
29-DEC-08
next_day:
SQL>SELECT  NEXT_DAY(SYSDATE,’WED’)FROM DUAL;
05-JAN-09
add_months:
SQL>SELECT ADD_MONTHS(SYSDATE,2)FROM DUAL;
28-FEB-09
last_day:
SQL>SELECT LAST_DAY(SYSDATE)FROM DUAL;
31-DEC-08
months_between:
SQL>SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)FROM EMP;
  4
Least:
SQL>SELECT LEAST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Greatest:
SQL>SELECT GREATEST('10-JAN-07','12-OCT-07')FROM DUAL;
10-JAN-07
Trunc:
SQL>SELECT TRUNC(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
Round:
SQL>SELECT ROUND(SYSDATE,'DAY')FROM DUAL;
28-DEC-08
to_char:
SQL> select to_char(sysdate, "dd\mm\yy") from dual;
24-mar-05.
to_date:
SQL> select to_date(sysdate, "dd\mm\yy") from dual;
24-mar-o5.

0 comments:

Post a Comment