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
- How to Search in all Columns for all tables in a database for Date Value in SQL Server
- How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database
- How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
- How to Get Row Count Of All The Tables In SQL Server Database
- How to search for a String in all Columns in all tables in SQL Server Database
- How to find and Replace Text in all the columns in all the tables in SQL server Database
- How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
- How to Find Duplicate Records in All the Tables in SQL Server Database
- How to Find & Delete Duplicate Records on All Tables in SQL Server Database
- How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server
- Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server
- How to List all Missing Identity Values for all Tables in SQL Server Database
- How to Find Max and Min Values for all the columns for all the tables in SQL Server Database
No comments:
Post a Comment