Replace function combined with ltrim and rtrim

trim vs ltrim and rtrim sql server
trim function
ltrim and rtrim in sql
ssrs trim function
sql replace
ltrim and rtrim in sql w3schools
trim in sql server 2014
ltrim(rtrim not working in sql server 2008)

Can someone please help me to understand the following code:

REPLACE(LTRIM(RTRIM(dbo.UFN_SEPARATES_COLUMNS(CompletionDetails, 3, ','))), '.', '') AS BuildRequestID,

Does it say remove all trailing and leading spaces, then replace 3 with comma. Next, if there is ., replace it with ' '?

It does not at any point replace 3 with ,.

We can make all this easier to follow by formatting the full expression to cover multiple lines:

REPLACE(
    LTRIM(RTRIM(
        dbo.UFN_SEPARATES_COLUMNS(CompletionDetails, 3, ',')
    )) 
    ,'.', ''
) AS BuildRequestID,

Expressions like this have to read from the inside out. So we start with this inner-most part:

dbo.UFN_SEPARATES_COLUMNS(CompletionDetails, 3, ',')

This UFN_SEPARATES_COLUMNS() function is not part of Sql Server, but was added by someone at your organization or as part of the vendor software package for the database you're looking at. But I'm confident based on inferences and the link (found via Google) it will treat CompletionDetails as delimited text, where the delimiter is a comma (based on the 3rd ',' argument) and returns the 3rd field (based on the 2nd 3 argument, where counting starts at 1 rather than 0). As CSV parsers go, this one is particularly naive, so be very careful what you expect from it.

Then we use LTRIM() and RTRIM() to remove both leading and trailing blanks from the field. Not all whitepsace is removed; only space characters. Tabs, line feeds, etc are not trimmed. Sql Server 2017 has a new TRIM() function that can handle wider character sets and do both sides of the string with one call.

The code then uses the REPLACE() function to remove all . characters from the result (replaces them with an empty string).

Guru: LTRIM + RTRIM > TRIM, The functions to which I refer are LTRIM (left trim) and RTRIM (right trim). can't ignore embedded characters, but the REPLACE function can. LEFT then truncates the string to that position. The REPLACE functions as it does in your original example, removing the 'Parts - ' portion (note I've included the space following the hyphen). LTRIM and RTRIM only remove leading/trailing spaces respectively, so they won't be useful here.

The code is trimming the leading and trailing spaces via the LTRIM() and RTRIM() functions of whatever is returned from the function dbo.x_COLUMNS... i.e. dbo.x_COLUMNS(CompletionDetails, 3, ','). LTRIM is left, RTRIM is right.

It then is replacing all periods (.) with nothing via the REPLACE() function.

So in summary, it's removing all periods from the string and the leading and trailing spaces.

SQL Server 2017: TRIM not only a replacement of RTRIM and LTRIM, The result is what I expected with a deleted A at the beginning of the string. My last test is with both characters together in the same Trim function  SET field1 = LTRIM(RTRIM(field1)) This one simple change shaved 40 minutes off my job! Why? Because REPLACE() has to crawl across each field character by character to find each what needs to be replaced. The TRIM functions are only concerned about white space at the beginning and end of the field and so, in this case, they are much faster.

The LTRIM removes leading spaces. RTRIM removes trailing spaces. REPLACE removes the period.

Declare @Val Char(20) = '   Frid.ay    '

Select REPLACE(
                LTRIM(
                      RTRIM(
                         @Val   --dbo.x_COLUMNS(CompletionDetails, 3, ',')
                            )
                      ), '.', ''
              ) 

Result

BuildRequestID
--------------
Friday

Rtrim, Ltrim & replace unable to get rid of spaces - MSDN, SET @position = 1 SET @string = 'New Moon' WHILE @position <= DATALENGTH(@string) BEGIN SELECT ASCII(SUBSTRING(@string,  This function is a replacement of RTRIM and LTRIM to delete all space character before and after a string. SELECT Name, RTRIM(LTRIM(Name)) as RLTrim_name, TRIM(Name) as Trim_name FROM [GoT].[dbo].[character-deaths2] Where Name like '%Aegon Targaryen%'

remove all trailing and leading spaces, then replace 3 with comma. Next, if there is ., replace it with ' '

No it does not say that. But this does:

REPLACE(REPLACE(LTRIM(RTRIM(CompletionDetails)), '3', ','), '.', ' ')

it's not clear if you want . replaced by ' ' or ''. I used the 1st case, you can change it as you like. It's easier to understand like this:

  1. remove all trailing and leading spaces: LTRIM(RTRIM(CompletionDetails))
  2. replace 3 with comma: REPLACE( ?, '3', ',')
  3. replace it with ' ': REPLACE(? , '.', ' ') or REPLACE(? , '.', '')

SQL TRIM function, In this article, we will explore the SQL TRIM function with examples. get a new built-in function to trim both leading and trailing characters together We can still use RTRIM and LTRIM function with SQL Server 2017 as well. SQL replace: How to replace ASCII special characters in SQL Server · Working  The L in LTRIM stands for “Left”, and is the opposite of the RTRIM or “Right” Trim function Finally, the Oracle RTRIM function removes a specified character from the right side of a string. This is most often used when you want to remove spaces from the right side of the string.

PostgreSQL TRIM Function, You will learn how to use PostgreSQL TRIM function to remove the longest string The BTRIM function is the combination of the LTRIM() and RTRIM() functions. Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 12 SQL Server database books, 33 Pluralsight courses and has written over 5100 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 17+ years of hands-on experience, he holds a Masters of Science degree and a number of database certifications.

Oracle TRIM, LTRIM & RTRIM Function Guide, FAQ & Examples, The Oracle TRIM, LTRIM, and RTRIM functions are used to remove characters from the Or it is mixed with spaces and tab characters (CHR(9))? Or, you can use the REPLACE function to remove all spaces between words  SQL TRIM(), LTRIM(), RTRIM() functions trim the char of the string. SQL TRIM() Function. SQL TRIM() function remove all specified trim char from beginning and

SQL Server RTRIM() Function, The RTRIM() function removes trailing spaces from a string. Note: Also look at the LTRIM() function. Syntax. RTRIM(string). Parameter Values. Parameter  LTRIM removes ‘S.’ from S. MacDonald and the ‘S’ from both Sawyer and Steadman, but not the period from H. Bender. This is because LTRIM searches, character-by-character, for the set of characters you specify in the trim_set argument. If the first character in the string matches the first character in the trim_set, LTRIM removes it

Comments
  • What is dbo.x_COLUMNS(CompletionDetails, 3, ',')? Whatever that spits out, you are then trimming any whitespace from either end of it with your LTRIM(RTRIM()) Then you are replacing any periods '.' with nothing '' with that REPLACE(<stuff>, '.', '') function. Again though, we don't know what dbo.x_COLUMNS(CompletionDetails, 3, ',') is doing. That looks to be a custom function in your system so you'd have to tell us.
  • @JNevill, here is what I have: ...(dbo.UFN_SEPARATES_COLUMNS(CompletionDetails, 3, ',').... I don't know what dbo.UFN_SEPARATES_COLUMNS is!
  • Google says UFN_SEPARATES_COLUMNS() came from here: social.technet.microsoft.com/wiki/contents/articles/…
  • @JoelCoehoorn -- you used google -- that's cheating, you're supposed to guess.
  • @Hogan I used google after. The original question had a different name for the function that doesn't show any meaningful results.
  • Thx @Joel Coehoorn