Explain character-manipulation functions? Explains its different types in SQL.

Character-manipulation functions are used to change, extract, and alter the character string. When one or more characters and words are passed into the function, the function will perform its operation on those input strings and return the result.

The following are the character manipulation functions in SQL:

A) CONCAT: This function is used to join two or more values together. It always appends the second string into the end of the first string. For example:

Input: SELECT CONCAT (‘Information-‘, ‘technology’) FROM DUAL;

Output: Information-technology

B) SUBSTR: It is used to return the portion of the string from a specified start point to an endpoint. For example:

Input: SELECT SUBSTR (‘Database Management System’, 9, 11) FROM DUAL;

Output: Management

C) LENGTH: This function returns the string’s length in numerical value, including the blank spaces. For example:

Input: SELECT LENGTH (‘Hello Javatpoint’) FROM DUAL;

Output: 16

D) INSTR: This function finds the exact numeric position of a specified character or word in a given string. For example:

Input: SELECT INSTR (‘Hello Javatpoint’, ‘Javatpoint’);

Output: 7

E) LPAD: It returns the padding of the left-side character value for right-justified value. For example:

Input: SELECT LPAD (‘200′, 6,’*’);

Output: ***200

F) RPAD: It returns the padding of the right-side character value for left-justified value. For example:

Input: SELECT RPAD (‘200′, 6,’*’);

Output: 200***

G) TRIM: This function is used to remove all the defined characters from the beginning, end, or both. It also trimmed extra spaces. For example:

Input: SELECT TRIM (‘A’ FROM ‘ABCDCBA’);

Output: BCDCB

H) REPLACE: This function is used to replace all occurrences of a word or portion of the string (substring) with the other specified string value. For example:

Input: SELECT REPLACE ( ‘It is the best coffee at the famous coffee shop.’, ‘coffee’, ‘tea’);

Output: It is the best tea at the famous tea shop.