In this post, I am sharing one solution to remove extra spaces from a string value of SQL Server.
Before a few days ago, I was doing data migration from a CSV sources to a SQL Server Table. After that, I have checked my destination table and found lots of extra space in one of my string column.
Using this script we can remove all unwanted spaces from a string of SQL Server.
First, Create sample table and data:
CREATE TABLE tbl_RemoveExtraSpaces
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 ')
Script to SELECT string without Extra Spaces:
,[Name] AS StringWithSpace
,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) AS StringWithoutSpace
Script to Remove or UPDATE original string without Extra Spaces:
UPDATE tbl_RemoveExtraSpaces SET Name = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32))))
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.