How to find leading and trailing spaces in SQL Server ?

You may have faced a situation where you are asked to find values in a column with leading or trailing spaces or remove the leading and trailing spaces from values.

In this post we learn how to find values with leading and trailing spaces in SQL Server.

Lets create a sample table and insert some records into this table.

Create table dbo.RawData
(RowId int,
StudName varchar(100),
Code varchar(5)
)

insert into dbo.RawData
(RowId, StudName, Code)
values
(1, ' Raj singh', 'RT67'),
(2, 'Sanjay kumar ', 'SJ98'),
(3, 'Rohit Singh', 'RJK6'),
(4, 'Amit Dutt ', 'AM98'),
(5, ' Chandan Kumar', 'CH67'),
(6, ' Manish', 'MF67')

You can see table RawData output, there are few records in StudName columns with leading and trailing spaces.

select * from dbo.RawData

Now write and execute below query to find leading and trailing spaces in StudName column.

select * from dbo.RawData
where StudName like ' %' or StudName like '% '

You can see the query output, it returns records those are having leading and trailing spaces.

Now we will remove the leading or trailing spaces from values.

Update dbo.RawData SET StudName = TRIM(StudName)
where StudName like ' %' or StudName like '% '

Lets execute the below query again to ensure that leading and trailing spaces are removed.

select * from dbo.RawData
where StudName like ' %' or StudName like '% '

You can see, it does not return any records that means leading and trailing spaces are removed.

Now you can check the records in a table.

select * from dbo.RawData

Also Read 

SQL LIKE CLAUSE

SQL TRIM

SQL Server Q & A

 6,456 total views,  2 views today