SQL Server: Tips and Tricks - 2 (2017)

How to find values with Leading or Trailing Spaces in SQL Server Table

Scenario:

You are working as SQL Server Developer / TSQL Developer with Mortgage Company. You are preparing script for data cleaning. You need to find all the records with leading and trailing spaces. Once you found them then you need to write update statement to remove leading and trailing spaces.

Solution:

Let's create our test temp table with some test data. I have included leading and trailing space in 4 values.

--Create Test Table

Create table #Temp( Name VARCHAR(100))

Insert into #Temp

Select 'NoSpace' AS NAme

Union

Select ' SingleSpaceAtStart' AS Name

Union

Select ' TwoSpacesAtStart' AS Name

Union

Select 'SingleSpaceAfter ' AS Name

Union

Select 'TwoSpacesAfter ' as Name

Let's go ahead and run our Select Query to find Leading and Trailing spaces in our data.

Select * from #Temp

where name like '% ' --Will provide us values with Trailing space/s

or name like ' %' --Will provide us values with leading Space/s

Our query returned only 4 records, as there is no leading or trailing space for first record.

Let's use the update statement to update the records and remove the leading and trailing spaces.

--Update the records and remove leading and trailing spaces

Update #Temp

Set Name=LTRIM(RTRIM(Name))

where name like '% '

or name like ' %'

Four records will be updated. If I will run select query again to check the leading or trailing spaces in column, I will get no records as the spaces are removed and records are updated.

Go ahead and run just to confirm

Select * from #Temp

where name like '% ' --Will provide us values with Trailing space/s

or name like ' %' --Will provide us values with leading Space/s

All materials on the site are licensed Creative Commons Attribution-Sharealike 3.0 Unported CC BY-SA 3.0 & GNU Free Documentation License (GFDL)

If you are the copyright holder of any material contained on our site and intend to remove it, please contact our site administrator for approval.