How to Delete Leading Empty Space in a SQL Database Table Using Ms SQL Server Management Studio

How to delete leading empty space in a SQL Database Table using MS SQL Server Management Studio

This will remove leading and trailing spaces

Update tablename set fieldName = ltrim(rtrim(fieldName));

some versions of SQL Support

Update tablename set fieldName = trim(fieldName);

If you just want to remove leading

update tablename set fieldName = LTRIM(fieldName);

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

Removing trailing spaces and whitespaces from SQL Server column

Check the below;

1. Find any special characters like char(10), char(13) etc in the field value.
2. Check the status of ANSI_PADDING ON. Refer this MSDN article.

Remove all spaces from a string in SQL Server

Simply replace it;

SELECT REPLACE(fld_or_variable, ' ', '')

Edit:
Just to clarify; its a global replace, there is no need to trim() or worry about multiple spaces for either char or varchar:

create table #t (
    c char(8),
    v varchar(8))

insert #t (c, v) values 
    ('a a'    , 'a a'    ),
    ('a a  '  , 'a a  '  ),
    ('  a a'  , '  a a'  ),
    ('  a a  ', '  a a  ')

select
    '"' + c + '"' [IN], '"' + replace(c, ' ', '') + '"' [OUT]
from #t  
union all select
    '"' + v + '"', '"' + replace(v, ' ', '') + '"'
from #t 

Result

IN             OUT
===================
"a a     "     "aa"
"a a     "     "aa"
"  a a   "     "aa"
"  a a   "     "aa"
"a a"          "aa"
"a a  "        "aa"
"  a a"        "aa"
"  a a  "      "aa"

How can I remove empty lines in SQL Server Management Studio (SSMS)?

It is not built in. The find and replace can be used with regex's and someone crafty may have a solution for that.

How can I remove trailing spaces from a SQL Server 2008 query when exporting to csv?

Although I haven't figured out why a CSV created by SQL Server 2008 behaves differently in Excel than one created in SQL Server 2005, I have found a solution to my immediate problem.

First of all, the trailing spaces don't seem to be the problem. However, SS2008 is adding them where SS2005 does not and it is annoying. But this is not the main cause of my problem.

When right clicking the resulting CSV file then clicking Open With -> Excel, it was autoformatting into badly formatted columns (where SS2005's CSV file opens with all the data in a single column). To solve this, I just open Excel first, then open the CSV file from within Excel. This gives me the column formatting dialogue I was missing.

How eliminate the tab space in the column in SQL Server 2008

Try this code

SELECT REPLACE([Column], char(9), '') From [dbo.Table] 

char(9) is the TAB character

Delete empty rows

DELETE FROM table WHERE edit_user IS NULL;

---

Related Topics