SQL TUTORIAL - CHARACTER FUNCATIONS

SINGLE ROW FUNCTIONS PART-2



www.ocptechnology.com



SQL Character Function

Character Function: Accept only Character type values and return in character and number both.

I. Concat: Joins two or more values together & using minimum two parameters with concat.

Ex. SELECT  CONCAT('OCP','TECHNOLOGY') FROM DUAL;

Above query will return OCPTECHNOLOGY as output.

II. Substr: Retrieve a string of specified length.

Ex. SELECT SUBSTR('OCPTECHNOLOGY',1,3) FROM DUAL;

Above query will return OCP as output.

III. Length: Calculate a lenth of a string as a numeric value.

Ex. SELECT LENGTH('OCPTECHNOLOGY') FROM DUAL;

Above query will return 13 as output.

IV. Instr: Shows the position of a named character in number.

Ex. SELECT INSTR('OCPTECHNOLOGY','T') FROM DUAL;

Above query will show you 3 as output.

V. Lpad: left pad shows the character value just right justified.

Ex. SELECT LPAD(SAL,10,'*') FROM EMP;

Suppose salary is 35000 rupees, show above query will return like *****35000 as output.

VI. Rpad: right pad shows the character value jus left justified.

Ex. SELECT RPAD(SAL,10,'*') FROM EMP;

Suppose salary is 35000 rupees, show above query will return like 35000***** as output.

VII. Replace: It’s just replace the value same like MS WORD.

Ex. SELECT REPLACE('BLACK AND BLUE','BL','J') FROM DUAL;

Above query will return JACK AND JUE as output. 

VIII. Trim: It’s just trim a character or heading or both from a character string.

Ex. SELECT TRIM('O' FROM 'OCPTECHNOLOGY') FROM DUAL;

Above query will return CPTECHNOLOGY as output.