LTRIM function giving same results as TRIM

Question

LTRIM function giving same results as TRIM

michael.leach2015

SSCommitted

Points: 1597
More actions
April 21, 2019 at 8:41 pm

#3503617
I am testing this:
```
SELECT LEN(TRIM('     SSMS     '))
SELECT LEN(LTRIM('     SSMS     '))
SELECT LEN(RTRIM('     SSMS     '))
```
The results I am getting are:

4
4
9
For the LTRIM function, I expected to get 9, not 4. With LTRIM seems like the spaces to the right are ignored. If that is the case, then it does the same thing as TRIM. If that is the case then why would SQL even have a TRIM function?
What is happening here with the LTRIM function? After truncating the leading spaces, it should count SSMS plus the 5 spaces that follow, resulting in 9 for the length.

On a similar note, the following isn't working either:
```
SELECT LEN(LTRIM('.' FROM '.....SSMS.....'))
```
So it seems like there is something unusual about how the LTRIM function is being used, but most likely I am overlooking something.
- This topic was modified 5 years, 8 months ago by michael.leach2015.
- This topic was modified 5 years, 8 months ago by michael.leach2015.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Try using DATALENGTH instead:

SELECT DATALENGTH(TRIM('     SSMS     '))
SELECT DATALENGTH(LTRIM('     SSMS     '))
SELECT DATALENGTH(RTRIM('     SSMS     '))

It still isn't likely to behave the way you are assuming with trailing spaces. Refer to this article for more information:

https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces

The other query that isn't working shouldn't be working. It's not valid and should error, not sure what you are trying to do there.

Sue

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

To add to what Sue has stated, LEN doesn't include trailing spaces in its count. Here's the note from Books Online. The emphasis is mine.

Returns the number of characters of the specified string expression, excluding trailing blanks.

Just a bit of a suggestion... never assume that you know what a function will actually do or return just by its name. Lookup the function and make sure especially when something unexpected occurs like with the problem you posed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 3

Sue with the following code

SELECT LEN(LTRIM('.' FROM '.....SSMS.....'))

I was attempting to TRIM/remove all the '.' to the left of the letters SSMS, which would return: SSMS.....

The TRIM function can remove characters that are not spaces, so I figured that LTRIM could do the same but only from the left side. Can LTRIM and RTRIM remove specific characters?

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

michael.leach2015 wrote:

Sue with the following code
SELECT LEN(LTRIM('.' FROM '.....SSMS.....'))
I was attempting to TRIM/remove all the '.' to the left of the letters SSMS, which would return: SSMS..... The TRIM function can remove characters that are not spaces, so I figured that LTRIM could do the same but only from the left side. Can LTRIM and RTRIM remove specific characters?

TRIM can remove characters other than spaces, LTRIM and RTRIM an only remove spaces

😎

This works fine

SELECT LEN(TRIM('.' FROM '.....SSMS.....'))

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Eirikur Eiriksson wrote:

TRIM can remove characters other than spaces, LTRIM and RTRIM an only remove spaces 😎 This works fine
SELECT LEN(TRIM('.' FROM '.....SSMS.....'))

Good lord... talk about a violation of the expected syntax for a function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

The newly introduced T-SQL TRIM() function is almost compatible with other vendor's flavors of SQL such as Oracle, PostgeSQL, Netezza etc.

😎

Of course it's not perfect as it is lacking the BOTH, LEADING and TRAILING directives, different number of characters can be passed. This means that during code migration, it is still on the incompatibility list 🙁

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

I guess I don't understand the need for the parameters of BOTH, LEADING, and TRAILING. Apparently the industry standard has changed over time because, for me, TRIM() always meant both sides and if you wanted something else, you'd use either LTRIM() or RTRIM(). And having to use the word FROM in a function gives me the willies.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 8

He he, a "standard" is these days a "temporal" thing, changes quicker than my girls mind in a shoe shop 😉

😎

IIRC, TRIM was in ANSII SQL 2003, maybe earlier but I can't remember.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 9

TRIM can remove characters other than spaces, LTRIM and RTRIM an only remove spaces

So if I wanted to remove specific characters from the left or right, how could I do that since LTRIM and RTRIM won't work? Are there any T-SQL functions that can do that?

David Burrows SSC Guru Points: 65143 More actions · Answer 10

If you are talking about a contiguous leading non space character then a combination of PATINDEX and STUFF should work.

For trailing use REVERSE, PATINDEX and LEFT.

Far away is close at hand in the images of elsewhere.
Anon.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 11

Here is a quick example

😎

USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TSTR VARCHAR(50) = '.....SSMS.....';
SELECT 
    STUFF(@TSTR,1,PATINDEX('%[^\.]%',@TSTR) - 1,'') AS TRIM_LEFT
   ,LEFT(@TSTR,(1 + DATALENGTH(@TSTR) - PATINDEX('%[^\.]%',REVERSE(@TSTR)))) AS TRIM_RIGHT
;

And the output

TRIM_LEFT TRIM_RIGHT

---------- -----------

SSMS..... .....SSMS

David Burrows SSC Guru Points: 65143 More actions · Answer 12

Eirikur Eiriksson wrote:

Here is a quick example 😎

USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TSTR VARCHAR(50) = '.....SSMS.....';
SELECT 
    STUFF(@TSTR,1,PATINDEX('%[^\.]%',@TSTR) - 1,'') AS TRIM_LEFT
   ,LEFT(@TSTR,(1 + DATALENGTH(@TSTR) - PATINDEX('%[^\.]%',REVERSE(@TSTR)))) AS TRIM_RIGHT
;

And the output TRIM_LEFT TRIM_RIGHT ---------- ----------- SSMS..... .....SSMS

Or

    STUFF(@TSTR,1,PATINDEX('%[^\'+LEFT(@TSTR,1)+']%',@TSTR) - 1,'') AS TRIM_LEFT
   ,LEFT(@TSTR,(1 + DATALENGTH(@TSTR) - PATINDEX('%[^\'+RIGHT(@TSTR,1)+']%',REVERSE(@TSTR)))) AS TRIM_RIGHT

Far away is close at hand in the images of elsewhere.
Anon.