SQL Tutorial : DRL / DQL - CASE INSENSITIVE QUERY SEARCH, SUBSTR, CONCAT, INSTR, TRIM

SQL Tutorial : DRL / DQL - CASE INSENSITIVE QUERY SEARCH,, SUBSTR, CONCAT, INSTR, TRIM

Let's learn about next topic: CASE INSENSITIVE QUERY SEARCH,, SUBSTR, CONCAT, INSTR, TRIM

CASE INSENSITIVE QUERY SEARCH:

***********************************

- SELECT LAST_NAME,SALARY

FROM EMPLOYEES
WHERE UPPER(LAST_NAME)=UPPER('KInG')

* CHARACTER MANIPULATION

-    LENGTH, CONCAT , SUBSTR, INSTR,

-    LTRIM, RTRIM,TRIM ,REPLACE,
-    LPAD, RPAD

- SELECT LENGTH('BANGALORE CITY')

FROM DUAL

CONCAT:

*********

- used to concatenate the string

- ONLY 2 ARGUMENTS

- SELECT CONCAT(LAST_NAME,FIRST_NAME) EMPNAME1 ,

LAST_NAME||FIRST_NAME ||SALARY EMPNAME2
FROM EMPLOYEES
WHERE DEPARTMENT_ID=30

SUBSTR:

********

- this will give a substring
- we can pass index , starting index and last index


- SELECT SUBSTR('TAJMAHAL',1)
FROM DUAL => TAJMAHAL

- SELECT SUBSTR('TAJMAHAL',1,3)

FROM DUAL => TAJ

- SELECT SUBSTR('TAJMAHAL',4,4)

FROM DUAL => MAHA

- SELECT SUBSTR('TAJMAHAL',-5,4)

FROM DUAL => MAHA (-5 POSITION FROM LAST)

INSTR:

- GIVES THE POSITION OF THE GIVEN CHARACTER

-  FROM FIRST

- SELECT INSTR('TAJMAHAL','A')

FROM DUAL => 2

- SELECT INSTR('TAJMAHAL','A',1,2)

FROM DUAL => 5

- SELECT INSTR('TAJMAHAL','A',2,3)

FROM DUAL=> 7

- SELECT LPAD('SUPER',10,'*')

FROM DUAL

- SELECT RPAD('SUPER',10,'$')

FROM DUAL

TRIM REMOVES SPACES :

***********************

- SELECT LENGTH(' ABC ') ,LENGTH(TRIM(' ABC ') )

FROM DUAL

- SELECT LTRIM(' ABC '),RTRIM(' ABC ')

FROM DUAL

- SELECT TRIM('H' FROM 'HHEEHHEEHH')

FROM DUAL

- SELECT TRIM(TRAILING 'H' FROM 'HHEEHHEEHH')

 FROM DUAL

- SELECT TRIM(LEADING 'H' FROM 'HHEEHHEEHH')

 FROM DUAL

- SELECT REPLACE ('TAJMAHAL','TAJ',' MYSORE')
FROM DUAL