Sunday, March 19, 2017

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.

No comments:

Post a Comment