CHARACTER BUILT IN FUNCTIONS

UPPER()

UPPER('john') = JOHN

LOWER()

LOWER('John')=john

INITCAP

SELECT INITCAP(firstname), INITCAP(lastname)
 FROM customers
  WHERE LOWER(lastname) = 'nelson'

LENGTH()

LENGTH('JOHN')=4

LPAD and RPAD

SELECT firstname, LPAD(firstname, 12, ' '), LPAD(firstname, 12 '*')
FROM customers
WHERE firstname LIKE 'J%'

LTRIM and RTRIM

SELECT lastname, address, LTRIM(address, 'P.O. BOX')
FROM customers
WHERE state ='FL';

TRANSLATE

SELECT name, TRANSLATE(name, ',', '-'), TRANSLATE(name, ',A', '-a')
FROM CONTACTS;

REPLACE

SELECT lastname, address, REPLACE(address, 'P.O.', 'POST OFFICE')
FROM customers
WHERE state ='FL';

CONCAT

SELECT firstname, lastname, CONCAT('customernumber: ', customer#) "Number"
FROM customers
WHERE state = 'FL';

SUBSTR()

RETURNS PART OF A STRING. START FROM THE 2ND POSITION AND RETURN 3 CHARACHTERS

SUBSTR('JAMES',2,3)=AME

INSTR=

Searches a string fr a specified set of characters or substring and then returns the first character position in which the substring is found

SELECT name, INSTR(name, ',') "First comma",
INSTR(name, ',',10) "Start read and position 10",
INSTR(name, ',',1,2) "Second Comma",

DECODE()

RETURNS THE VALUE MATCHING THE CONDITION TAKE THE 20 IN THE FRONT AND RETURN THE CORRESPONDING STRING

DECODE(20,10,'TEN',20,'TWENTY',30,'THIRTY') = TWENTY

Back To Top- Home - Category