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

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