SQL> SELECT * FROM EMP;
ID NAME DOJ EMAIL
---------- --------------- --------- ------------------------------
1 KAMLESH 07-MAY-15 LEARNINGMARTINDIA@GMAIL.COM
2 RANJIT 11-DEC-11 LEARNINGMARTINDIA@GMAIL.COM
3 RAJESH 11-FEB-01 LEARNINGMARTINDIA@GMAIL.COM
4 SHIVAM 01-FEB-21 LEARNINGMARTINDIA@GMAIL.COM
HOW TO SELECT DOMAIN NAME FROM EMAIL
SQL> SELECT SUBSTR(EMAIL,INSTR(EMAIL,'@',1,1)+1) AS DOMAIN_NAME FROM EMP;
DOMAIN_NAME
--------------------------------------------------------------------------------
GMAIL.COM
GMAIL.COM
GMAIL.COM
GMAIL.COM
HOW TO SELECT COMPANY NAME FROM EMAIL
SQL> SELECT SUBSTR(EMAIL,INSTR(EMAIL,'@',1,1)+1,
INSTR(EMAIL,'.',1,1)-INSTR(EMAIL,'@',1,1)-1)
AS COMPANY_NAME FROM EMP;
COMPANY_NAME
--------------------------------------------------------------------------------
GMAIL
GMAIL
GMAIL
GMAIL
SQL> SELECT CONCAT(NAME,EMAIL) FROM EMP;
CONCAT(NAME,EMAIL)
---------------------------------------------
KAMLESHLEARNINGMARTINDIA@GMAIL.COM
RANJITLEARNINGMARTINDIA@GMAIL.COM
RAJESHLEARNINGMARTINDIA@GMAIL.COM
SHIVAMLEARNINGMARTINDIA@GMAIL.COM
INITCAP
ID NAME DOJ EMAIL
---------- --------------- --------- ------------------------------
1 KAMLESH 07-MAY-15 LEARNINGMARTINDIA@GMAIL.COM
2 RANJIT 11-DEC-11 LEARNINGMARTINDIA@GMAIL.COM
3 RAJESH 11-FEB-01 LEARNINGMARTINDIA@GMAIL.COM
4 SHIVAM 01-FEB-21 LEARNINGMARTINDIA@GMAIL.COM
HOW TO SELECT DOMAIN NAME FROM EMAIL
SQL> SELECT SUBSTR(EMAIL,INSTR(EMAIL,'@',1,1)+1) AS DOMAIN_NAME FROM EMP;
DOMAIN_NAME
--------------------------------------------------------------------------------
GMAIL.COM
GMAIL.COM
GMAIL.COM
GMAIL.COM
HOW TO SELECT COMPANY NAME FROM EMAIL
SQL> SELECT SUBSTR(EMAIL,INSTR(EMAIL,'@',1,1)+1,
INSTR(EMAIL,'.',1,1)-INSTR(EMAIL,'@',1,1)-1)
AS COMPANY_NAME FROM EMP;
COMPANY_NAME
--------------------------------------------------------------------------------
GMAIL
GMAIL
GMAIL
GMAIL
SQL> SELECT CONCAT(NAME,EMAIL) FROM EMP;
CONCAT(NAME,EMAIL)
---------------------------------------------
KAMLESHLEARNINGMARTINDIA@GMAIL.COM
RANJITLEARNINGMARTINDIA@GMAIL.COM
RAJESHLEARNINGMARTINDIA@GMAIL.COM
SHIVAMLEARNINGMARTINDIA@GMAIL.COM
INITCAP
SQL> SELECT INITCAP('learningmart.blogspot.in') FROM DUAL;
INITCAP('LEARNINGMART.BL
------------------------
Learningmart.Blogspot.In
UPPER
SQL> SELECT UPPER('learningmart.blogspot.in') FROM DUAL;
UPPER('LEARNINGMART.BLOG
------------------------
LEARNINGMART.BLOGSPOT.IN
LOWER :
SQL> SELECT LOWER('LEARNINGMART.BLOGSPOT.IN') FROM DUAL;
LOWER('LEARNINGMART.BLOG
------------------------
learningmart.blogspot.in
LENGTH :
SQL> SELECT LENGTH('LEARNINGMART.BLOGSPOT.IN') FROM DUAL;
LENGTH('LEARNINGMART.BLOGSPOT.IN')
----------------------------------
24
RPAD : ALLOWS YOU TO PAD THE RIGHT SIDE OF A COLUMN WITH ANY SET OF CHARACTERS
SQL> SELECT RPAD('LEARNINGMART.BLOGSPOT.IN',15,'*') FROM DUAL;
RPAD('LEARNINGM
---------------
LEARNINGMART.BL
LPAD : ALLOWS YOU TO PAD THE LEFT SIDE OF A COLUMN WITH ANY SET OF CHARACTERS
SQL> SELECT LPAD('LEARNINGMART.BLOGSPOT.IN',15,'*') FROM DUAL;
LPAD('LEARNINGM
---------------
LEARNINGMART.BL
LTRIM : THIS WILL TRIM OFF UNWANTED CHARACTERS FROM THE LEFT END OF STRING
SQL> SELECT LTRIM(' LERNING') FROM DUAL;
LTRIM('
-------
LERNING
RTRIM : THIS WILL TRIM OFF UNWANTED CHARACTERS FROM THE RIGHT END OF STRING
SQL> SELECT RTRIM('LERNING ') FROM DUAL;
RTRIM('
-------
LERNING
TRIM : THIS WILL TRIM OFF UNWANTED CHARACTERS FROM THE BOTH END OF STRING
SQL> SELECT TRIM(' LERNING ') FROM DUAL;
TRIM('L
-------
LERNING
TRANSLATE : THIS WILL REPLACE THE SET OF CHARACTERS, CHARACTER BY CHARACTER
SQL> SELECT TRANSLATE('INDIA','IN','XY') FROM DUAL;
TRANS
-----
XYDXA
REPLACE : THIS WILL REPLACE THE SET OF CHARACTERS STRING BY STRING
SQL> SELECT REPLACE('LEARNING','LE','E') FROM DUAL;
REPLACE
-------
EARNING
SQL> SELECT REPLACE('LEARNING','LE') FROM DUAL;
REPLAC
------
ARNING
ASCII : THIS WILL RETURN DECIMAL REPRESENTATION IN THE DATABASE CHARACTER SET OF THE FIRST CHARACTER OF THE STRING
SQL> SELECT ASCII('A') FROM DUAL;
ASCII('A')
----------
65
CONCAT : TO COMBINE TWO STRINGS
SQL> SELECT 'HOW' || 'ARE' || 'YOU' FROM DUAL;
'HOW'||'A
---------
HOWAREYOU
CHR : RETURNS BINARY EQUIVALENT TO STRING .
SQL> SELECT CHR(97) FROM DUAL;
C
-
a
DECODE : DECODE WILL ACT AS VALUE BY VALUE SUBSTITUTION . FOR EVERY VALUE OF FIELD , IT WILL CHECKS FOR A MCATCH IN THE SERIES OF IF/ THEN TESTS.
SQL> SELECT * FROM S1;
NAME NO ADDRESS
--------------- ---------- -----------------
kamlesh 1 bangalore
mohan 2 patna
ravi 3 delhi
SQL> SELECT NAME,ADDRESS,DECODE(NO,1,'FRIST',2,'SECOND',3,'THIRD','NONE') POSITION FROM S1;
NAME ADDRESS POSITI
--------------- ----------------- ------
kamlesh bangalore FRIST
mohan patna SECOND
ravi delhi THIRD
GREATEST : RETURNS THE GREATEST STRING
SQL> SELECT GREATEST('KAMLESH','RAVI','SURESH') FROM DUAL;
GREATE
------
SURESH
LEAST : RETURNS LEAST STRING
SQL> SELECT LEAST('KAMLESH','RAVI','SURESH') FROM DUAL;
LEAST('
-------
KAMLESH
0 comments:
Post a Comment