CODEWITHSUNDEEP
sqlsql-server
Eric Labashosky
Eric Labashosky

Reputation: 29981

How to trim a string in SQL Server before 2017?

In SQL Server 2017, you can use this syntax, but not in earlier versions:

SELECT Name = TRIM(Name) FROM dbo.Customer;

Upvotes: 170

Views: 385869

Answers (9)

dfilteau
dfilteau

Reputation: 19

Or in the absence of TRIM() in the older versions, you could use LTRIM(RTRIM(somestring)) to achieve the desired result.

Upvotes: 0

Poke82
Poke82

Reputation: 11

SELECT REPLACE(name, ' ', '') FROM Customer

Upvotes: -1

Reejuta Sharmin
Reejuta Sharmin

Reputation: 11

To trim any set of characters from the beginning and end of a string, you can do the following code where @TrimPattern defines the characters to be trimmed. In this example, Space, tab, LF and CR characters are being trimmed:

Declare @Test nvarchar(50) = Concat (' ', char(9), char(13), char(10), ' ', 'TEST', ' ', char(9), char(10), char(13),' ', 'Test', ' ', char(9), ' ', char(9), char(13), ' ')

DECLARE @TrimPattern nvarchar(max) = '%[^ ' + char(9) + char(13) + char(10) +']%'

SELECT SUBSTRING(@Test, PATINDEX(@TrimPattern, @Test), LEN(@Test) - PATINDEX(@TrimPattern, @Test) - PATINDEX(@TrimPattern, LTRIM(REVERSE(@Test))) + 2)

Upvotes: 1

Kai-Ove Böhnisch
Kai-Ove Böhnisch

Reputation: 41

Extended version of "REPLACE":

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(REPLACE("Put in your Field name", ' ',' '))),'''',''), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), CHAR(160), '') [CorrValue]

Upvotes: 3

razon
razon

Reputation: 4060

SELECT LTRIM(RTRIM(Replace(Replace(Replace(name,'   ',' '),CHAR(13), ' '),char(10), ' ')))
from author

Upvotes: 8

rahularyansharma
rahularyansharma

Reputation: 10777

in sql server 2008 r2 with ssis expression we have the trim function .

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.

you can find the complete description on this link

http://msdn.microsoft.com/en-us/library/ms139947.aspx

but this function have some limitation in itself which are also mentioned by msdn on that page. but this is in sql server 2008 r2 

TRIM("   New York   ") .The return result is "New York".

Upvotes: 1

Ben Hoffstein
Ben Hoffstein

Reputation: 103395

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

Upvotes: 310

onedaywhen
onedaywhen

Reputation: 57093

I assume this is a one-off data scrubbing exercise. Once done, ensure you add database constraints to prevent bad data in the future e.g. 

ALTER TABLE Customer ADD
   CONSTRAINT customer_names__whitespace
      CHECK (
             Names NOT LIKE ' %'
             AND Names NOT LIKE '% '
             AND Names NOT LIKE '%  %'
            );

Also consider disallowing other characters (tab, carriage return, line feed, etc) that may cause problems.

It may also be a good time to split those Names into family_name, first_name, etc :)

Upvotes: 8

Kibbee
Kibbee

Reputation: 66162

To Trim on the right, use:

SELECT RTRIM(Names) FROM Customer

To Trim on the left, use:

SELECT LTRIM(Names) FROM Customer

To Trim on the both sides, use:

SELECT LTRIM(RTRIM(Names)) FROM Customer

Upvotes: 67

Related Questions