Manipulation Functions in SQL

SQL String functions are considered to the popular and significant SQL tools. In this tutorial, let’s check out the different ways through which you can make use of the different manipulation functions and operations on strings.

There are plenty of manipulation SQL functions which help you in editing the string data. Some of the function helps to identify the string length whereas others eradicate the unneeded characters or spaces in the strings. The SQL manipulations function provides plenty of opportunities to work and transform the strings so that making your own code is made more easier and effective. It also makes the code simple to understand.

Top Manipulation functions & Strings in SQL:

All SQL developers work with these manipulation functions and strings. Here are some of the common manipulation functions or string functions used in SQL.

CONCAT()

We have already the tutorial of CONCAT and concatenation in our previous blogs. Here is the Syntax used for CONCAT() function.

CONCAT(first_name, second_name,......n_name)

The CONCAT function helps to integrate more than one string to a single string. All entry-name input should be in the data types like NCHAR, VARCHAR, or CHAR. Let me explain this with simple examples.

SELECT CONCAT (‘Gangboard Institute is reliable,’ trustworthy,’ ‘and result-focused!’)
FROM DUAL;

The output of the above command will look like

Gangboard Institute is reliable trustworthy and result-focused!

In the above output, you can check out how CONCAT has taken three separate strings and combined them to a new string in SQL. It’s used when you need to present the information in your database in a simple to understand way.

Now, let’s assume we have a table called student that stores Students ID numbers, names, joined date, and the course they study. Let’s say, we need to display each of the student’s data in a way that everyone can understand. The best option is to create a sentence below.

SELECT CONCAT(name, ‘joined the gangboard institute on’, date, ‘in’, course name)
FROM STUDENT
WHERE student_id = 220

Let’s assume name as Vivaan, date to be 20/12/2019 and the course name as DevOps training. The output after executing the above string will look like

Vivaan joined the gangboard institute on 20/12/2019 in DevOps training course.

IN the SQL database system, the CONCAT is replaced by ‘||’, which is the string concatenation symbol. The concatenation symbol is suitable with any SQL standards but this will not work perfectly in all database systems. For example, you need to make use of the operator “+” in the SQL server.

SUBSTR

The syntax of SUBSTR manipulation function in SQL is

SUBSTR(char, position, length)

SUBSTR is identified as a substring or takes one portion from a string and then return them. CHAR specifies what should be used as the substring source. The position is defined as the starting of the substring, and length indicates the length of the substring. Let’s look over the syntax below.

SELECT SUBSTR(‘Gangboard Institute”. 12, 9)
FROM DUAL

The output of the above query will be Institute

The function is mostly utilized in adding characters to the string and deleting the characters from a large string. Let me explain this with a practical example. I am creating a table named Offers that contain the Offer list. The Offer ID includes letters that identify both the offer type as well as the numbers. Check out the following syntax

Id
AB82
BR21
JJ34
WU32

Now let’s say that you need to add an underscore between the above string in the offer ID. You can make use of the SUBSTR or CONCAT to make it easier. The syntax will look like

UPDATE Offers SET id= CONCAT(SUBSTR(id, 1, 2), ‘_’ , SUBSTR(id, 3);
Id
AB_82
BR_21
JJ_34
WU_32

UPDATE command will help to change the offers table. CONCAT helps to combine the string portion that is created by the dual SUBSTR functions. The second SUBSTR will hold every character that is located after the position indicated in the second parameter and not the third parameter.

As discussed easier, not every database system uses the identical name for the SQL Server string manipulating functions. We need to make use of the SUBSTRING, a SUBSTR function in the SQL server but you can use the same syntax.

TRIM

Let’s check the syntax of TRIM below.

TRIM([[LEADING|TRAILING|BOTH] character FROM] edit_char)

The main goal of the TRIM to delete all related characters from the initial part, which is the LEADING, the last part, which is the TRAILING, or else both part, which is edit_char.

The instruction includes plenty of parameters. The first thing you need to do is select the string part where you are going to cut down the characters. If you are not removing, TRIM will delete the specific characters from both of the specific string to end of edit_char. The second thing is that you need to tell the characters that should be deleted. If this parameter is not used, the function will just delete the space characters. So you can define the final string.

Let’s see this with a practical example.

Delete leading and trailing spaces:
SELECT TRIM(‘         Institute2019          ‘)
FROM DUAL;

The output will be

“Institute”

Let’s see another example.

SELECT TRIM(“   FROM    ‘       Gangboard2019           ‘)
FROM DUAL;

The output of the above string will be

“Gangboard2019”

Delete Trailing ‘2019’ from the string”

SELECT TRIM(TRAILING ‘2019’ FROM ‘Gangboard2019’)
FROM DUAL;

The output of the above-executed string will be

“Gangboard”

Delete both leading and trailing ‘20’ from the string:

Let’s check this with an example.

SELECT TRIM (BOTH ‘20’ FROM ‘2019Gangboard20’)
FROM DUAL;

The output will be

“19GangboardInstitute”

CHR and ASCII:

Let’s look over the syntax

CHR(Character_code)
ASCII(Single_character)

Both CHR and ASCII are two different functions with opposite use. ASCII has a single character and then return the number code. For eg let’s take “V” is 54. Let’s say a group of characters in a string is entered, now the ASCII functions will return a value just for the first character and then ignore the added characters. In the case of CHR, it takes the code number of ASCII and returns the specified character. For example, if you give it as 54, and it will return a “V”.

Let’s check out an example, you need to find people whose first name starts with C. You are making use of the ASCII code number to perform this. The first thing to find the A’s ASCII equivalent. Let’s look over the syntax below.

SELECT ASCII(‘C’)
FROM DUAL;

The output will be 40 as we have taken 40 as the value of C. Now it’s easy to find their records.

SELECT *
FROM Employees
WHERE SUBSTR(Second_name, 1,1) - CHR(40);

The output will be

First name	Second name	age
Vivaan	Senorita	29
Deena	Soundary	34
Samuel	Johnson	26

REPLACE

The syntax of REPLACE manipulation functions in SQL is

REPLACE(entry_name, string_searching, string_replace)

REPLACE helps you to remove or replace data selectively from inside a string SQL. The String_searching value is replaced with string_replace when it returns an entry_char. Let’s assume if the value of the string_replace is null, then the values which match the string_searching are completely deleted from the whole entry string.

Let me explain this with simple examples. Let’s assume, you need to update a record part.

SELECT REPLACE (‘Gangboard Institute is result-oriented!’, ‘Reliable’, ‘trust-worthy!’)
FROM DUAL;

When we execute the above syntax, then the output will look like

Gangboard Institute is trust_worthy!

In the above example, you can see REPLACE has changed the result-oriented in “Gangboard Institute is result-oriented” to “trust_worthy. So that you get the final output as Gangboard Institute is trust_worthy!

Let me explain with a practical example. I have created a table called register which stores the student’s name. A student name sabthika changed her last name as she got married. The REPLACE manipulation functions enable us to update the record of sabthika easily without any hassle.

UPDATE register
SET name = REPLACE(name, ‘Poornesh’, ‘Sajin’)
WHERE name LIKE ‘Sabthika%’

The output of the above-executed function will result in Sabthika Poornesh will be officially changed to Sabthika Sajin.

Apart from the above manipulation functions in SQL, there are many other functions namely

• Instr
• Length
• Rtrim
• Rpad
• Lpad
• Initcap
• Lower
• Upper

I hope the above tutorial helped you to know about the manipulation functions in SQL. For any queries, you can let us know through the comment section below

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL