RTRIM in SQL – What Does it Do?

One of the Ways to TRIM

RTRIM function is used to remove trailing spaces from a specified string. TRIM function is used to remove specified prefix or suffix from a string. It removes all spaces from a text string except the single spaces between words. But since TRIM cannot be applied in SQL, the same results are achieved through the functions RTRIM and LTRIM. Before you get into the intricacies of the RTRIM function, if you feel you need to learn how to build applications and generate reports, it will be a good idea to check out Introduction to SQL Training and SQL Database for Beginners.

SQL is in Great Demand

As the demand for database developers are rising in different sections of the industry, you will call the shots if you learn SQL. Most database vendors are aligning their databases with the ANSI/ISO SQL standard. So when you learn how to handle SQL databases, you’ll be prepared to move from one database to another. The SQL language hasn’t changed much over the decades and once you understand the basics of SQL, you can keep upgrading and building your knowledge so that you can add more features and functionality to your database. By knowing SQL, as a DBA, you’ll be able to make better decisions and move poorly performing code out of the database. Through experience and practice you will soon be able to spot the problems even before they strike.

Syntax of RTRIM Explained Through Examples

The general syntax of the RTRIM function is explained below. The SQL RTRIM function name is not case sensitive and using all lowercase letters will provide the same results.

Syntax:

RTRIM (character_expression)

where, character_expression is an expression of character or binary data. A character_expression can be a constant, variable, or column.

Return type of RTRIM function is a varchar datatype.

Examples of RTRIM Function

Example 1: Use of RTRIM function in SELECT clause

Syntax:

SELECT RTRIM(‘Words as Examples   ‘)

Output:

Words as Examples

In the above example, extra trailing spaces or spaces on the right have been removed.

Example 2:

Syntax:

DECLARE @string_trim varchar(75);

SET @string_trim = ‘Three spaces inserted after the period in this sentence.   ‘;

SELECT @string_trim + ‘ Second sentence.’;

SELECT RTRIM(@string_trim) + ‘ Second sentence.’;

GO

Output:

————————————————————————-

Three spaces inserted after the period in this sentence.   Second sentence.

————————————————————————-

Three spaces inserted after the period in this sentence. Second sentence.

The first output shows three trailing spaces at the end of the first sentence, but these spaces are removed from the second output after the RTRIM function is applied.

You may often come across databases where names have been entered with trailing spaces. If you need a single syntax to strip the extra spaces while entering the information, then using this syntax will be appropriate:

Syntax:

INSERT INTO table (name_column) values

(RTRIM($nameVariable);

Let’s consider another scenario. If you inherit a table with a column already polluted with strings containing extra then you do a one time data correction with this query:

Syntax:

UPDATE table SET name_column = RTRIM(name_column)[, col2 =RTRIM(col2)[, col3 =RTRIM(col3)[…]]]

RTRIM and LTRIM – The Duo Used for Trimming

If we have to get the same effect of a TRIM function in SQL and need to remove both the leading and trailing spaces from a string, we need to combine or nest the RTRIM () and LTRIM () functions. If we combine them in a statement, then it will work and will not throw an error.

Let’s see how to achieve this.

Syntax:

DECLARE @String2 NVARCHAR(MAX)

SET @String2 = ‘  Sentence  ‘

SELECT @String2 OriginalString, RTRIM(LTRIM(@String2)) TrimmedValue

GO

The output of the above script will show up as the word ‘Sentence’ with no spaces before or after the word.

Conclusion:

RTRIM is a simple but yet powerful function which you can use in your SQL queries to manage your data and database better. As you work extensively on database programs, you gradually unravel the different ways to overcome the challenges you come across while working on databases. You can be ahead in the race and will be able to build up a successful career if you keep yourself updated on the latest developments in the world of database. Many firms adopt SQL for their convenience and security of their records. Hence, they look to appoint professionals who have knowledge in SQL and have undergone training in SQL. An SQL certification helps to validate your knowledge and gives you better job opportunities. So if you want to add more feathers to your cap and want certification courses to appear on your resume, then Microsoft SQL Server 2012 Certification Training Exam 70-461 will prepare you for the MCSA or MCSE certification exams.