This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a solution to remove the extra spaces from a string value of SQL Server.
Before a few days ago, I was doing data migration from a CSV sources into the SQL Server Table. I checked my destination table and found lots of extra space in one of my string column.
Using below script, we can remove all unwanted spaces from a string of SQL Server.
Create the sample table:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE tbl_RemoveExtraSpaces ( Rno INT ,Name VARCHAR(100) ) GO INSERT INTO tbl_RemoveExtraSpaces VALUES (1,'I am Anvesh Patel') INSERT INTO tbl_RemoveExtraSpaces VALUES (2,'Database Research and Development ') INSERT INTO tbl_RemoveExtraSpaces VALUES (3,'Database Administrator ') INSERT INTO tbl_RemoveExtraSpaces VALUES (4,'Learning BIGDATA and NOSQL ') GO |
SELECT string without Extra Spaces:
|
1 2 3 4 5 |
SELECT [Rno] ,[Name] AS StringWithSpace ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) AS StringWithoutSpace FROM tbl_RemoveExtraSpaces |
The Result:
Script to Remove or UPDATE original string without Extra Spaces:
|
1 |
UPDATE tbl_RemoveExtraSpaces SET Name = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) |
