TRIM() in T-SQL
-
Comments posted to this topic are about the item TRIM() in T-SQL
-
Steve,
Do you have an error in the first query under the 'Choosing what gets Trimmed' section? Currently it shows the query as:
SELECT Result = '[' + TRIM(n) + ']' ,
FirstLetter = ASCII(SUBSTRING(TRIM(n), 1, 1)),
LastLetter = ASCII(SUBSTRING(TRIM(n), LEN(TRIM(n)) - 1, 1))
FROM( VALUES (',Broncos, Raiders, Chargers, Chiefs ') ,
(',Texans, Jaguars, Colts, Titans, ' ) ,
('Steelers,Ravens,Browns,Bengals,') ,
(',Patriots,Dolphins,Bills,Jets'))
AS a (n);
GOAnd the results show rows with missing commas at the beginning and end, but the actual TRIM commands "TRIM(n)"does not list that commas should be removed (which I think is what you intended).
An interesting new feature in SQL Server though, thanks for the article highlighting that it finally exists.
-
Yep. Thanks, forgot the TRIM(',' FROM n) part.
-
I must admit, when I saw the title, TRIM() in T-SQL my first thought was, "boring!"
That said - I was quite impressed and found the article to be quite informative. Great work Steve."I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
AS I dont have version 2017, will it remove two leading or trailing commas instead of one as you showed in your example. Would it Remove two leading 'patriots' in the string?
Thanks, great article----------------------------------------------------
-
-
"I don't know why they made this decision instead of structuring things like this..."
Because ANSI SQL. -
Seems like a troublesome or confusing function since from your test for ‘Patriots’ the example result removed the ‘ts’ from Jets, but not ‘ts’ from Colts. -- i must have missed something from the translation.
-
"I don't know why they made this decision instead of structuring things like this..."
Because now you don't have to overload TRIM() with 2 definitions (one with 1 parameter, one with 2 parameters)
-
'Colts' is in the middle of the string, and TRIM() only removes characters from the beginning and the end.
-
I can see this being useful to replace LTRIM(RTRIM()), or leading/trailing characters like commas.
Beyond that it seems to get dangerous if you are looking for more than one character to replace, as in your Patriots example. Even with case-sensitive collation, you could get unexpected results if one of your strings started with, for instance, "Panthers".
-
The reason for the "strange" syntax is ANSI: PostgreSQL and Oracle already have this function and it is the same way. Great article to expose more people to this function! Thanks!
Jared
CE - Microsoft -
As a Bills fan, I approve of the removal of a certain team. 😉
Great Article Steve! I always looked at string manipulation in SQL somewhat akin to working with Linux. It might not make everything simple but everything is all there if you know how to put it together.
Cheers
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Boss Man:-
Typo here:-
The documentation notes this is a strig function that takes two parameters, but not as most other functions.
Should read:-
The documentation notes this is a string function that takes two parameters, but not as most other functions.
Thanks,
Daniel
-
Thanks, updated the text.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply