What is SQL Server TRIM() function?

Filed Under: SQL Server
SQL SERVER TRIM() FUNCTION

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.

Output:

PythonJournalDev

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.

Output:

$PythonJournalDev$

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;

Output:

id	City
1	Pune
2	Satara
3	California

Conclusion

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.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages