How to Use a Trim Function in SQL Server

How to trim a string in SQL Server before 2017?

SELECT LTRIM(RTRIM(Names)) AS Names FROM Customer

TRIM is not a recognized built-in function name

TRIM is introduced in SQL Server (starting with 2017).

In older version of SQL Server to perform trim you have to use LTRIM and RTRIM like following.

DECLARE @ss varchar(60)
  SET @ss = ' admin '

  select RTRIM(LTRIM(@ss))

If you don't like using LTRIM, RTRIM everywhere, you can create your own custom function like following.

   CREATE FUNCTION dbo.TRIM(@string NVARCHAR(max))
    RETURNS NVARCHAR(max)
     BEGIN
      RETURN LTRIM(RTRIM(@string))
     END
    GO

How to use a TRIM function in SQL Server

You are missing two closing parentheses...and I am not sure an ampersand works as a string concatenation operator. Try '+'

SELECT dbo.COL_V_Cost_GEMS_Detail.TNG_SYS_NR AS [EHP Code], 
dbo.COL_TBL_VCOURSE.TNG_NA AS [Course Title], 
LTRIM(RTRIM(FCT_TYP_CD)) + ') AND (' + LTRIM(RTRIM(DEP_TYP_ID)) + ')' AS [Course Owner]

TRIM function on SQL Server 2014

UPDATE MYTABLE SET MYFIELD = LTRIM(RTRIM(MYFIELD));

However, field type must be varchar() and not text.
Otherwise you get "Argument data type text is invalid for argument 1 of rtrim function"

Remove Trailing Spaces and Update in Columns in SQL Server

Try
SELECT LTRIM(RTRIM('Amit Tech Corp '))

LTRIM - removes any leading spaces from left side of string

RTRIM - removes any spaces from right

Ex:

update table set CompanyName = LTRIM(RTRIM(CompanyName))

Use TRIM in JOIN

It looks like you want:

select x.code, x.code1, x.code2, y.branch, z.country
from xxx.1002 x
left join yyy.1003 y on x.code= y.code
left join zzz.100  z on trim(z.code1) = x.code1

trim() removes spaces on both ends of the string. If you want to remove only leading spaces, you can do: trim(leading ' ' from z.code1).

Note that I used more meaningful table aliases, in order to make the query easier to write and read.

I would also reommend against using all-digits table names: in Oracle, non-quoted identifiers must begin begin with an alphabetic character.

---

Related Topics