Is there a particular order for using the LTRIM, RTRIM with a LEFT function? Example - I only want the Left 35 from a field and want to trim any unnecessary off it. Is this the correct Syntax - LEFT(RTRIM(LTRIM(Guar.First)),35) or should I use the TRIM's then the LEFT?
As you're on 2019, use the TRIM() function instead. Do the trim first.
LEFT(TRIM())
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 10, 2022 at 3:22 pm
Thank you!
May 10, 2022 at 3:33 pm
or should I use the TRIM's then the LEFT?
That is what you are doing; the LTRIM would be processed first, then the RTRIM, and finally LEFT. Perhaps you mean should you LEFT and then trim, which would be LTRIM(RTRIM(LEFT(Guar.First,35))).
These, however, would not give the same results. Assuming you want the left most characters, and then trim any whitespace off, then you want the LEFT in the inside. If you want the trim the string first and then get the 35 left most characters you want the trims inside.
You don't actually say which you want, but here's an example:
DECLARE @SomeString varchar(50) = ' ' + REPLICATE('a',30) + ' x y z ';
SELECT CONCAT('"',TRIM(LEFT(@SomeString,35)),'"') AS LeftThenTrim,
CONCAT('"',LEFT(TRIM(@SomeString),35),'"') AS TrimThenLeft;This gives the following results (I have added the double quotes):
LeftThenTrim TrimThenLeft
------------------------------------- -------------------------------------
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa x "
The first takes the 35 left most characters, giving 3 spaces, followed by 30 a's, followed by 2 more spaces, and then trims it; giving 30 a characters.
The second, however, trims the value first, then takes the 35 left most characters from that, resulting in 30 a's, followed by 3 spaces, an x, and then another space.
So, to answer which is correct, it depends what result you are after.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 10, 2022 at 4:52 pm
Thom has provided good logic. What do you want? 35 characters from the beginning and then remove any whitespace? Or remove whitespace, then take the 35 characters from the left.
October 6, 2022 at 9:04 am
TRIM and then Left would work if you are using SQL Server 2017 or above, since, SQL Server TRIM works the same as LTRIM and RTRIM combined, as shown by the 'Thom A' in his example.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply