Character Manipulation Functions in SQL

Read this Oracle Tutorial Point article to know behaviour of Character, LOWER, UPPER, INITCAP and CONCAT functions in SQL with examples.

• Character Function : It accept Character input and can return both Character and Number value.
• Number Function : It accept Number as input and return Number values.
• Date Function: Operate on values of data type can return both Date and Number.
• Conversation Function: Convert a value from one data type to another.
• General Function : NVL,DECODE ---- Operates on any datatype and can return any datatype.

Character Functions:

Character functions returns the character values, unless stated. They return the datatype VARCHAR2 and it’s limited to a length of 4000 bytes. If the return value go beyond the length, then the return value is truncated, without an error. The functions are categorised as,

• CHARACTER MANIPULATION.
• CASE CONVERSION.

Character Manipulation Functions:

LOWER Function : LOWER function converts Alpha character values to LOWERcase. The return value has the same datatype as argument char type(CHAR or VARCHAR2).

Syntax:

• LOWER (EXPRESSION / COLUMN)

Examples:

SQL> SELECT LOWER('SUBBAREDDY NALLAMACHU') FROM DUAL;

SQL> SELECT NAME, LOWER('MY INFORMATION') FROM DUAL;

SQL> SELECT NAME, LOWER(NAME) FROM EMP WHERE JOB = 'MANAGER';

SQL> SELECT 'The' || NAME||'s Designation is ' || JOB FROM EMP WHERE OWER(JOB)='MANAGER';

UPPER Function:
Upper function converts the Alpha character values to capital letters. The return value has the same datatype as the argument char.

Syntax:

• UPPER (Column/Expression)

Examples:

SQL> SELECT UPPER('Oracle Corporation') FROM DUAL;

SQL> SELECT NAME, UPPER('My Information') FROM DUAL;

SQL> SELECT NAME, UPPER(NAME) FROM EMP WHERE JOB = 'MANAGER';

SQL> SELECT NAME, LOWER(NAME), UPPER(NAME) FROM EMP WHERE JOB = 'MANAGER';

SQL> SELECT NAME,JOB FROM EMP WHERE JOB=UPPER('Manager');

INITCAP Function:
INITCAP function converts the Alpha character values to capital for the first letter of every word, keeping all other letters in Lowercase. Words are delimited by white spaces or character those are not alphanumeric.

Syntax:

• INITCAP(Column / Expression)

Examples:

SQL> SELECT INITCAP('subbareddy nallamachu') FROM DUAL;

SQL> SELECT 'The Job Title for '|| INITCAP(NAME)|| ' is ' ||LOWER(JOB) Details FROM EXP;

SQL> SELECT NAME, LOWER(NAME),INITCAP(NAME),UPPER(NAME) FROM EMP;

CONCAT Function:
CONCAT function concatenates the first character value to the second character value. Only two parameters accept. It returns the character data type

Syntax:

• CONCAT(Column1/Expr1, Column2/Expr2)

Examples:

SQL> SELECT CONCAT('ORACLE','CORPORATION') FROM DUAL;

SQL> SELECT NAME,JOB,CONCAT(NAME,JOB) FROM EMP WHERE DNO=10;

SQL> SELECT CONCAT('The Employee Name is ', INITCAP(NAME)) AS 'Employee Names ' FROM EMP

 WHERE DNO IN(10,30);

Case conversion functions will discuss in coming articles.