PAD, TRIM, REPLACE, TRANSLATE and ASCII Functions in SQL

Read this Oracle Tutorial Point article to know about LPAD & RPAD functions,TRIM function, REPLACE function, TRANSLATE function, CHR function and ASCII functions in SQL.

LPAD Function:
Pads the character value left justified to a total width of n character position. Space is the default padding character.

Syntax:        LPAD(Char1,n,Char2)

Examples:

SQL> SELECT LPAD('Page 1',15,'*.') FROM DUAL;

SQL> SELECT LPAD('Page 1',15) FROM DUAL;

SQL> SELECT NAME,LPAD(NAME,10,'-') FROM EMP WHERE SAL>=2500;

RPAD Function:
Pads the character value right justified to a total size of n character positions. Space is the default padding character.

Syntax:      RPAD(Char1,n, Char2)

Examples:

SQL> SELECT RPAD('Page 1',15,'*.') FROM DUAL;

SQL> SELECT RPAD('Page 1',15) FROM DUAL;

SQL> SELECT NAME,RPAD(NAME,10,'-') FROM EMP WHERE SAL>=2500;

TRIM Function:

TRIM function enables to trim heading or trailing characters or together from a character string. If Leading is defined concentrates on leading characters. If Trailing is specified focuses on trailing characters. If both or none is specified concentrates both on leading and trailing. Return the VARCHAR2 type.

Syntax:        TRIM(Trailing / Leading / Both, Trim character From Trim source)

Examples:

SQL> SELECT TRIM('R','RAAM') FROM DUAL;

SQL> SELECT TRIM('R','RAAMA') FROM DUAL;

SQL> SELECT TRIM('R','RRAAMRR') FROM DUAL;

SQL> SELECT TRIM(Trailing 'R' From ,'RRAAMRR') FROM DUAL;

SQL> SELECT TRIM(Leading 'R' From,'RRAAMRR') FROM DUAL;

SQL> SELECT TRIM(Both 'R' From,'RRAAMRR') FROM DUAL;

LTRIM Function:
LTRIM function enables trim heading character from a character string. All the left characters that seems within the set are removed.

Syntax:           LTRIM(Char,set)

Examples:

SQL>SELECT LTRIM('XYZ LAST WORD','XY') FROM DUAL;

SQL> SELECT JOB, LTRIM(JOB) FROM EMP WHERE JOB LIKE 'MANAGER';

RTRIM Function:

RTRIM function enables the trimming of trailing characters from a character string. All the rightmost characters that appear in the set are removed.

Syntax:       RTRIM(Char,set)

Examples:

SQL> SELECT RTRIM('SWIMMINGxyXxy','xy') FROM DUAL;

SQL> SELECT RTRIM(JOB,'R'),JOB FROM EMP WHERE LTRIM(JOB,'MAN') LIKE 'GER';

REPLACE Function:

REPLACE function returns the every incidence of search string replaced by the replacement string. If the replacement string is omitted or NULL, all incidences of search string are removed. It substitutes one string for another as well as to remove character strings.

Syntax:          REPLACE(Char,Search_Str,Replace_Str)

Examples:

SQL> SELECT REPLACE('Jack and Jue','J','Bl');

SQL> SELECT NAME,REPLACE(JOB,'MAN','DAM') FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,REPLACE(JOB,'MAN','EXECUTIVE') FROM EMP WHERE JOB='SALESMAN';

TRANSLATE Function:
TRANSLATE function is used to Translate Character by character in a String.

Syntax:   TRANSLATE(char,FROM,TO)

TRANSLATE function returns a char with all incidences of each character in ‘FROM’ replaced by its corresponding character in ‘TO’. Characters in char that aren't in FROM aren't replaced. The argument FROM can contain more characters than TO. If the extra characters seems in Char they're removed from the return value.

Examples:

SQL> SELECT  JOB,TRANSLATE(JOB,'P',' ') FROM EMP WHERE JOB='PRESIDENT';

SQL> SELECT JOB,TRANSLATE(JOB,'MN','OM') FROM EMP WHERE JOB='MANAGER';

SQL> SELECT JOB,TRANSLATE(JOB,'A','O') FROM EMP WHERE JOB='SALAESMAN';

CHR Function:
CHR function returns a character having the binary equivalent to ‘n’. CHR function returns the similar for ‘n’ in database character set or national character set.

Syntax:        CHR(n)   CHR(n using NCHAR_CS)

Examples:

SQL> SELECT CHR(67) || CHR(65) || CHR(84) SAMPLE FROM DUAL;

SQL> SELECT CHR(16705 Using NCHAR_CS) FROM DUAL;

ASCII Function:
ASCII function represents the decimal representation in the character database set of the first characters of the Char.

Syntax:     ASCII(Char)

Examples:

SQL> SELECT ASCII('A'), ASCII('APPLE') FROM DUAL;