Character Functions in SQL

Character Functions in SQL

Character functions takes the character inputs and returns either number or character. 

1.UPPER():

Upper function is used to convert a string or column values into UPPER case.
Syntax   : UPPER(value)                                                 
Example: SELECT UPPER('abc') FROM DUAL;         
Output   : ABC.                                                                
Example: SELECT UPPER('abc@123') FROM DUAL;
Output   : ABC@123                                                       

2. LOWER():

Lower function is used to converts the string or a column into LOWER case.
Syntax    :  LOWER(Value)                                                     
Example :  SELECT LOWER('ABC') FROM DUAL;           
Output    :  abc                                                                         
Example :  SELECT LOWER('ABC@123')  FROM DUAL;
Output    :  abc@123                                                               

3.INITCAP():

It returns initial letter as Capital letter and remaining letters as small. 
(OR)
It converts the initial letter of alpha character value to UPPER case and remaining all as LOWER.
Syntax    : INITCAP(value).                                                                           
Example : SELECT INITCAP('what is your name') FROM DUAL;             
Output    : What Is Your Name.                                                                      
Example : SELECT INITCAP('SQL_TUTORIAL_BOOK') FROM DUAL;
Output    : Sql_Tutorial_Book.                                                                       

4.LENGTH():

It returns the number data type as output. It counts the total length of the string including spaces. So system considers space also a character and returns the length. But system won't consider the null values.
Syntax    : LENGTH(value)                                           
Example : SELECT LENGTH('ABC') FROM DUAL; 
Output    : 3.                                                                    
Example : SELECT LENGTH('AB C') FROM DUAL;
Output    : 4 (Included space also)                                  
Example : SELECT LENGTH('') FROM DUAL;         
Output    : -           (output is null because we have passed input as null)

5. SUBSTR:

This will extract the portion of the string with in the passed string based on the last two parameters.
Syntax    : SUBSTR(Value, start_string, Lenth_of_extraction_String)
Example : SELECT SUBSTR('ABCDEFG',2,3) FROM DUAL;       
Output    : BCD                                                                                    
The above query is used to return the particular string based on the passed values. Asper the condition, 2nd letter is 'B' and then we have to consider three letter. An the same way if we are using '-' symbol means, System counts the letter positions from backwards.
Example : SELECT SUBSTR('ABCDEFG',-2,3) FROM DUAL;
Output    : FG                                                                                 
Example : SELECT SUBSTR('ABCDEFG',-5) FROM DUAL;   
Output    : CDEFG                                                                          

6. LPAD():

It will fills remaining spaces with specified characters on the left side of the given string. Here always 2nd parameter returns total length of the string.

Syntax    : LAPD(columnName(OR) string name, total Length, 'Filled characters')
Example : SELECT LAPD('ABCD',10,'#') FROM DUAL;                                     
Output    : ######ABCD                                                                                           
We have passed total length as 10. But out string length is 4. So system filled or replaced with specified character.

7.RAPD():

It will fills the remaining spaces with specified characters on the Right side. This functionality also same as LAPD.
Example : SELECT RPAD('ABCD',10,'#') FROM DUAL;
Output    : ABCD######                                                     

8. TRIM():

Oracle 8i introduced TRIM() function. It is used to remove left and right side of the value based on  the specified character.
Syntax    : TRIM('character' FROM 'string name')                                
Example : SELECT TRIM('S' FROM 'SSABCDESS') FROM DUAL;
Output    : ABCDE                                                                                 

Same Right and left TRIM. Left trim is used to trim the specified character on left side. Right trim is used to trim/remove the specific character on the right side.

9. TRANSLATE() and REPLACE();

Translate is used to replaces character by character where as replace is used to replace character by string or replaces string by string.
SQL> SELECT TRANSLATE('apple','ap','xy') FROM DUAL;
Output:   xyyle.                                                                           
In the above case, system replaces 'a' with 'x' and 'p' with 'y'.
SQL> SELECT REPLACE('apple','ap','xy') FROM DUAL;
Output: xyple                                                                         
In this case, system replaces 'ap' with 'xy'.


If you have any doubts, Please drop a comment.

                             Thank You