SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed.
You can see table RawData output, there are few records in StudName columns with leading and trailing spaces. Now write and execute below query to find leading and trailing spaces in StudName column. You can see the query output, it returns records those are having leading and trailing spaces.
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))
To just trim trailing spaces you should use
UPDATE
TableName
SET
ColumnName = RTRIM(ColumnName)
However, if you want to trim all leading and trailing spaces then use this
UPDATE
TableName
SET
ColumnName = LTRIM(RTRIM(ColumnName))
Well here is a nice script to TRIM all varchar columns on a table dynamically:
--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
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))
update MyTable set CompanyName = rtrim(CompanyName)
Use the TRIM SQL function.
If you are using SQL Server try :
SELECT LTRIM(RTRIM(YourColumn)) FROM YourTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With