SQL Server에서 열에서 후행 공백 제거 및 업데이트

SQL Server에서 열에서 후행 공백 제거 및 업데이트

---

SQL Server 테이블의 열에 뒤에 공백이 Company Name있습니다.

이 열의 모든 데이터에는 공백이 있습니다.

모든 것을 제거하고 후행 공백없이 데이터를 갖고 싶습니다.

회사 명은 "Amit Tech Corp "

회사 이름을 원합니다 "Amit Tech Corp"

---

시험 SELECT LTRIM(RTRIM('Amit Tech Corp '))

LTRIM -문자열의 왼쪽에서 선행 공백을 제거합니다

RTRIM -오른쪽에서 공백을 제거합니다

전의:

update table set CompanyName = LTRIM(RTRIM(CompanyName))

---

후행 공백을 자르려면 사용해야합니다.

UPDATE
    TableName
SET
    ColumnName = RTRIM(ColumnName)

그러나 모든 선행 및 후행 공백을 트리밍하려면 이것을 사용하십시오.

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

---

다음은 테이블의 모든 varchar 열을 동적으로 트리밍하는 멋진 스크립트입니다.

--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'

--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS 
WHERE   DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable

declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '

--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '

WHILE @i <= @tri 
BEGIN

    IF (@i = @tri)
        BEGIN
        set @comma = ''
        END
    SELECT  @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
    FROM    #tempcols
    where id = @i

    select @i = @i+1
END

--execute the entire query
EXEC sp_executesql @trimmer

drop table #tempcols

---

update MyTable set CompanyName = rtrim(CompanyName)

---

TRIM SQL 함수를 사용하십시오 .

SQL Server를 사용하는 경우 다음을 시도하십시오.

SELECT LTRIM(RTRIM(YourColumn)) FROM YourTable

---

If you are using SQL Server (starting with vNext) or Azure SQL Database then you can use the below query.

SELECT TRIM(ColumnName) from TableName;

For other SQL SERVER Database you can use the below query.

SELECT LTRIM(RTRIM(ColumnName)) from TableName

LTRIM - Removes spaces from the left

example: select LTRIM(' test ') as trim = 'test '

RTRIM - Removes spaces from the right

example: select RTRIM(' test ') as trim = ' test'

---

SQL Server does not support for Trim() function.

But you can use LTRIM() to remove leading spaces and RTRIM() to remove trailing spaces.

can use it as LTRIM(RTRIM(ColumnName)) to remove both.

update tablename
set ColumnName= LTRIM(RTRIM(ColumnName))

---

I had the same problem after extracting data from excel file using ETL and finaly i found solution there :

https://www.codeproject.com/Tips/330787/LTRIM-RTRIM-doesn-t-always-work

hope it helps ;)

---

Well, it depends on which version of SQL Server you are using.

In SQL Server 2008 r2, 2012 And 2014 you can simply use TRIM(CompanyName)

SQL Server TRIM Function

In other versions you have to use set CompanyName = LTRIM(RTRIM(CompanyName))

---

Example:

SELECT TRIM('   Sample   ');

Result: 'Sample'

UPDATE TableName SET ColumnName = TRIM(ColumnName)

---

SELECT TRIM(ColumnName) FROM dual;

---

To remove Enter:

Update [table_name] set
[column_name]=Replace(REPLACE([column_name],CHAR(13),''),CHAR(10),'')

To remove Tab:

Update [table_name] set
[column_name]=REPLACE([column_name],CHAR(9),'')

참고URL : https://stackoverflow.com/questions/14781999/remove-trailing-spaces-and-update-in-columns-in-sql-server