Hello, readers. In today’s article, we will be focusing on SQL Server TRIM() function in detail.
Working of SQL Server TRIM() function
In every programming language or query language, a set of functions are specified to manipulate and deal with the data input values.
On similar lines, SQL Server has in-built functions to deal with the String data values.
SQL Server TRIM() function helps eliminate the leading and trailing spaces from the data values contained in a table of the database.
The TRIM() function not only eliminates the white spaces but also deletes a particular character from the leading and trailing part of the string values.
Now let us understand the syntax of TRIM() function in the upcoming section.
Syntax of SQL Server TRIM() function
SQL Server TRIM() function accepts the data to be trimmed and the character value( in case we want to trim certain characters from the string data).
SELECT TRIM('characters' FROM 'data-value');
characters (OPTIONAL): Set of characters are passed which we want to eliminate from the starting and ending point of the string data values.
Having understood the structure of
TRIM() function, let us understand the implementation of the same through some examples.
Examples of TRIM() function
In the below example, we intend to trim the character ‘$’ from the input data value.
SELECT TRIM('$ ' FROM ' $PythonJournalDev$ ') AS Output;
As clearly seen below, the TRIM() function has completely eliminated ‘$’ from the leading and trailing part of the string.
TRIM() function detects and eliminates all the leading and trailing white-spaces too as seen below.
SELECT TRIM(' $PythonJournalDev$ ') AS Output;
In the above example, the character ‘$’ does not get eliminated as we have not passed the character to the parameter list. Here, the TRIM() function eliminates the spaces here.
In this example, we have created a table and inserted values into it. Further, we have used TRIM() function to trim the white spaces from all the data values of the passed column.
CREATE TABLE Info ( id INT PRIMARY KEY, City VARCHAR (255) NOT NULL ); INSERT INTO Info ( id, City ) VALUES ( 1, ' Pune ' ), ( 2, ' Satara ' ), ( 3, ' California ' ); UPDATE Info SET City = TRIM(City); Select * from Info;
id City 1 Pune 2 Satara 3 California
That’s all for this topic. Please feel free to comment below in case you come across any doubt.
For more such posts related to SQL Server, please do visit SQL Server JournalDev.