CODEWITHSUNDEEP
sqlt-sqltrim
ImmortalStrawberry
ImmortalStrawberry

Reputation: 6101

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

I've a table in a (MS) SQL database which has an Id column (identity, int) and a Name column (varchar(250)). However, the values in the name column contain (fairly random) leading and trailing spaces as I think they were cut and pasted from "something else" (no idea what!).

Is it possible in T-SQL to do the following:

update MyTable set Name = trim(name)

and have it update all the Name columns with the trimmed value?

Upvotes: 31

Views: 88420

Answers (8)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4253

check for isnull if you want the empty string returned for the null case

 UPDATE MyTable set Name = rtrim(ltrim(IsNull(name,'')))

Upvotes: 0

bmiller
bmiller

Reputation: 1783

Well as of SQL Server 2017 (14.x) and later there is a TRIM() function, so;

UPDATE MyTable set Name = TRIM(name)

Will work fine. Or in my case I also want to null empty fields after trimming.

UPDATE MyTable set Name = NULLIF(TRIM(name), '')

Upvotes: 10

silentgut
silentgut

Reputation: 87

Try this:

UPDATE [table] SET [column1] = REPLACE([column1],'i:0#.w|',' ')

Upvotes: 3

Widor
Widor

Reputation: 13275

Not quite - there's no TRIM() function available, so you have to use RTRIM() and LTRIM() (right- and left-trim, respectively):

UPDATE MyTable set Name = rtrim(ltrim(name))

Upvotes: 0

Marco
Marco

Reputation: 57593

You could try this:

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

Take a look here to create a function inside your database to use it faster

Upvotes: 3

cjk
cjk

Reputation: 46465

In SQL Server there is only RTRIM and LTRIM, but you can use them both together:

update MyTable set Name = RTRIM(LTRIM((name))

Upvotes: 2

Barry Jordan
Barry Jordan

Reputation: 2706

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

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

Upvotes: 67

StuartLC
StuartLC

Reputation: 107347

Try

update MyTable set Name = LTRIM(RTRIM((name))

Upvotes: 10

Related Questions