LTRIM usage with SQL server 2005

Question

I am a bit of an sql noob so please forgive. I can't seem to find a usage example of LTRIM anywhere.

I have a NVARCHAR column in my table in which a number of entries have leading whitespace - I'm presuming if I run this it should do the trick:

SELECT LTRIM( ColumnName)
  From TableName;

Will this give the desired result?

Answer 1

No, it will trim leading spaces but not all white space (e.g. carriage returns).

Edit It seems you are looking for an UPDATE query that will remove leading and trailing whitespace.

If by that you only mean "normal" spaces just use

    UPDATE TableName
   SET ColumnName = LTRIM(RTRIM(ColumnName ))

For all white space this should do it (from the comments here). Backup your data first!

    UPDATE TableName
   SET ColumnName = 
     SUBSTRING(
    ColumnName,
    PATINDEX('%[^ ' + char(09) + char(10) + char(13)  + char(20)  + ']%', 
    ColumnName),
    LEN(ColumnName) - PATINDEX('%[^ ' + char(09) + char(10) + char(13)  + char(20)  + ']%'
   , ColumnName) - 
    PATINDEX('%[^ ' + char(09) + char(10) + char(13)  + char(20)  + ']%', 
     REVERSE(ColumnName)) + 2) 

Answer 2

Did you run it to find out? It's just a select, it won't blow up your database. But, yes.

Select LTRIM(myColumn) myColumn
From   myTable

Should return the myColumn values with any leading whitespace removed. Note this is only leading whitespace.

EDIT To Update the column, with the above, you'd do:

Update myTable
Set myColumn = LTRIM(myColumn)
From myTable

Answer 3

Your example will work to remove the leading spaces. This will only select it from the database. IF you need to actually change the data in your table, you will need to write an UPDATE statement something like:

UPDATE TableName
SET ColumnName = LTRIM(ColumnName)

If you need to remove spaces from the right side, you can use RTRIM.

Here is a list of the string functions in SQL Server 2005 that I always refer to: http://msdn.microsoft.com/en-us/library/ms181984(v=SQL.90).aspx