Auto RTRIM and LTRIM on query result ?

Question

Hello,

I'm working on SQL server 2016.

Is there a specific syntax to have any queries returning data without any space before and after strings ?

I do not speak about how to store data, i do not need to change anything but the query returning.

For example i have a table with a column CHAR(40) and when i run a query (SELECT *) on it, i have all spaces after the string :

'TEST '

I need a command to run on SQL server to make any queries return :

'TEST'

Is anyone can help ?

Best regards

Answer 1

Hi @Fred Leclere ,

1. You are using a column with CHAR(40) data type. It means there are always 40 characters with trailing spaces regardless of the stored value.
2. Just modify that column as VARCHAR(40) data type. This way spaces are not allocated, not stored, and no need to TRIM() the value.

Answer 2

Thks but i can't change the column var type and keep CHAR(40)...

Answer 3

To answer the original question: No, there is no such syntax. You will need to use ltrim and rtrim. And I agree with Yithzak that it makes little sense to use char(40), unless it is actually values that are fixed 40 characters in size.

Answer 4

Hi @Fred Leclere ,

Welcome to the microsoft TSQL Q&A forum!

In SQL you declare a text value by telling the system how much space to reserve for it.

Char(40) allocates 40 bytes for every row.
Varchar(40) uses and offset plus the data, and each row takes up only the space it needs.

As Yitzhak Khabinsky said, You are using a column with CHAR(40) data type. It means there are always 40 characters with trailing spaces regardless of the stored value.

Therefore, if you do not use RTRIM and LTRIM, you cannot automatically remove spaces.

It is a good choice to use RTRIM and LTRIM in the view or cte.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.