What is Character Functions

Hello, friends in this article we are going to discuss how to use Character Functions in SQL. The character functions accept only Character type values and return in character and number both.

Type of Character Functions

There are Eight types of Character Functions in SQL.

1. CONCAT
2. SUBSTR
3. LENGTH
4. INSTR
5. LPAD
6. RPAD
7. REPLACE
8. TRIM

So, let's understand the working of each function one by one.

1. CONCAT

The CONCAT function joint two or more strings and display as a single string. We can use a minimum of two strings with the CONCAT function. For example, we have two strings like "OCP" and "TECHNOLOGY" and we want to display both strings together.

SQL> SELECT  CONCAT('OCP','TECHNOLOGY') FROM DUAL;

The output from the above query is "OCPTECHNOLOGY".

2. SUBSTR

The SUBSTR function Retrieve a part of the string as specified the length in function, as you can see in the below output. As we have a string like "OCPTECHNOLOGY" and we just want to display only the first three characters of the string.

SQL> SELECT SUBSTR('OCPTECHNOLOGY',1,3) FROM DUAL;

So, from the above query, we get the "OCP" as output.

3. LENGTH

The LENGTH function calculates the length of a string as a numeric value. Suppose we have a string like "OCPTECHNOLOGY" and we just want to count the letters of the sting, so you can use the length function.

SQL> SELECT LENGTH('OCPTECHNOLOGY') FROM DUAL;

The output is 13.

4. INSTR

The INSTR function shows the position of a given character in number. For example, you want to find out the position of "T" later from the "OCPTECHNOLOGY" string.

SQL> SELECT INSTR('OCPTECHNOLOGY','T') FROM DUAL;

Output from above query is 4.

5. LPAD

The LPAD (Left Pad) function will change the length of your column as you want for the session like you want your salary column with 10 digits. So LPAD solve your problem for the current session. The LPAD shows the character value just right justified.

SQL> SELECT LPAD(SAL,10,'*') FROM EMP;

In my case like my salary is 35000 rupees, so above query will return like *****35000 as output.

6. RPAD

The RPAD funcation (Right pad) is just opposite of LPAD function which shows the character value just left justified.

SQL> SELECT RPAD(SAL,10,'*') FROM EMP;

Suppose the salary is 35000 rupees, so above query will return like 35000***** as output.

7. REPLACE

The REPLACE function just replace the value, it is work same like MS WORD, Notepad etc.

SQL> SELECT REPLACE('BLACK AND BLUE','BL','J') FROM DUAL;

In above query i just replace the 'BL' with 'J' and the output is "JACK AND JUE".

8. TRIM

The TRIM function just trim a character or heading or both from a string.

SQL> SELECT TRIM('O' FROM 'OCPTECHNOLOGY') FROM DUAL;

From the above query, the TRIM function just trims the 'O' letter from 'OCPTECHNOLOGY' and you get 'CPTECHNOLOGY' as output.

Thanks for the Visit

SQL String Functions.

Substring Function in SQL.

Character functions in SQL.

Read - Single Row Functions