How to Trim a String in SQL Server Before 2017

How to trim a string in SQL Server before 2017?

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

SQL Server TRIM character

LEFT('BOB*', LEN('BOB*')-1)

should do it.

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"

How to Specify Trim Chars in SQL TRIM

You could potentially use PATINDEX() in order to get this done.

DECLARE @Text VARCHAR(50) = ', Well Crap';

SELECT STUFF(@Text, 1, PATINDEX('%[A-z]%', @Text) - 1, '');

This would output Well Crap. PATINDEX() will find first letter in your word and cut everything before it.

It works fine even if there's no leading rubbish:

DECLARE @Text VARCHAR(50) = 'Mister Roboto';

SELECT STUFF(@Text, 1, PATINDEX('%[A-z]%', @Text) - 1, '');

This outputs Mister Roboto

If there are no valid characters, let's say ContactName is , 9132124, :::, this would output NULL, if you'd like to get blank result, you can use COALESCE():

DECLARE @Text VARCHAR(50) = ', 9132124, :::';

SELECT COALESCE(STUFF(@Text, 1, PATINDEX('%[A-z]%', @Text) - 1, ''), '');

This will output an empty string.

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

Trimming text strings in SQL Server 2008

You do have an RTRIM and an LTRIM function. You can combine them to get the trim function you want.

UPDATE Table
SET Name = RTRIM(LTRIM(Name))

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))

Is it possible to Trim all the values in a column in a single statement?

MS SQL does not have a trim function. You'll need to use rTrim and lTrim together.

update MyTable set Name = lTrim(rTrim(name))

---

Related Topics