How to find values with Leading or Trailing Spaces in SQL Server Table - SQL Server / TSQL Tutorial

Scenario: Download Script

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

How to find values in SQL Server Table with leading or Trailing spaces 


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

How to update leading or trailing spaces in SQL Server - SQL Server / T-SQL tutorial


Check out our other posts on Data Analysis / Data Validation / Data Cleansing

No comments:

Post a Comment