13 Mayıs 2014 Salı

USE OF SOME PL/SQL FUNCTIONS IN SQL



ID1         ID2                  GNDR

5416             WOMEN WOMEN
5418     2290753          WOMEN

Here we have an instance table above and its name is GENDER also its coloumns names are ID1,ID2,GND4

LTRIM() function removes all specified characters from left side of first value shown inside function

SELECT LTRIM(gndr,'W') from GENDER;

Result;

OMEN WOMEN
OMEN

SELECT LTRIM (gndr,'WO') FROM GENDER

Result;
MEN WOMEN
MEN


RTRIM() function removes all specified characters from right side of first value shown inside function

SELECT RTRIM(gndr,'W') from GENDER;

Result;
OMEN WOMEN
OMEN


SELECT RTRIM (gndr,'EN') FROM GENDER

Result;
WOMEN WOM
WOM


NVL() function decides about returning value if specified value is null


SELECT NVL(ID2,0') FROM GENDER;

Result;

0
2290753

TRUNC() function to truncate some values from right side;

SELECT SYSDATE FROM DUAL;
Result;
will be
13/05/2014 15:59:55

SELECT TRUNC(SYSDATE) FROM DUAL;

Result;

13/05/2014

TRUNC() function also is used for some other reasons;

SELECT TRUNC(135.45,1) FROM DUAL;
Result;
135,4

SELECT TRUNC(135.45,0) FROM DUAL;

Result;
135

SELECT TRUNC(135.45,2) FROM DUAL;

Result;
135,45

SELECT TRUNC(135.45,-1) FROM DUAL;

Result;
130

SELECT TRUNC(135.45,-2) FROM DUAL;

Result;
100

SELECT TRUNC(135.45,-3) FROM DUAL;

Result;
0

Hiç yorum yok:

Yorum Gönder