T-Sql to replace multiple spaces with single space
T-Sql to replace multiple spaces with single space - Every developer face same problem many times when user enters continuous white space between two words. Here you will Know technique, How to Replace Multiple Spaces in String with Single Space in SQL Server?. Its too simple logic which is easy to use.
In previous article we were learn
How to Get table(s) - Database size in SQL Server?
In This article, We will learn Replace Function of Sql Server, And Knows technique which makes it more powerful, by sharing very common problem in sql server that is, How to Replace Multiple Spaces in String with Single Space in SQL Server.
its common problem many times when user enters continuous white space between two words which also increases extra white space in database.
We can replace such continuous blank space by using SQL server replace function.
The REPLACE() function is used to replaces all occurrences of a substring within a string, with a new substring.
Syntax:
REPLACE(string, old_string, new_string)
string: The original string
old_string: The string to be replaced
new_string: The new replacement string
For Example:
SELECT 'SQL SERVER'
UNION
SELECT REPLACE('SQL SERVER', 'SQL', 'Structured Query Language')
Go
OUTPUT:
SQL SERVER
Structured Query Language SERVER
USE AdventureWorks2014
Go
SELECT TOP 10 FirstName, REPLACE(FirstName,'a','z') ModifiedFirstName
FROM [Person].[Person] WHERE FirstName like 'a%'
Go
OUTPUT: FirstName ModifiedFirstName ------------- --------------- Adam zdzm Alex zlex Alexandra zlexzndrz Allison zllison Amanda zmzndz Amber zmber Andrea zndrez Angel zngel Alisha zlishz (10 row(s) affected)
Now What makes Replace function more powerful.
we can remove white space squences between two words as follows.
Step 1: Convert each and every white or blank space with {} angle braces.
DECLARE @str varchar(150)
SET @str='Hello friends, Welcome to swclass.com'
SELECT REPLACE(@str,' ','{}')
OUTPUT:
Hello{}{}{}friends,{}{}{}{}{}{}{}Welcome{}{}{}{}{}{}{}{}{}{}to{}{}{}{}{}{}{}{}{}swclass.com
(1 row(s) affected)
Step 2: Replace each and every }{ with ''.
DECLARE @str varchar(150)
SET @str='Hello friends, Welcome to swclass.com'
SELECT REPLACE(REPLACE(@str,' ','{}'),'}{','')
OUTPUT:
Hello{}friends,{}Welcome{}to{}swclass.com
(1 row(s) affected)
Step 3: Finally replace {} with on white or blank space ' '..
DECLARE @str varchar(150)
SET @str='Hello friends, Welcome to swclass.com'
SELECT REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')
OUTPUT:
Hello friends, Welcome to swclass.com
(1 row(s) affected)
T-Sql to replace multiple spaces with single space - Hope helps you a lot. If you have any suggestion or question please leave a comment. I will surely reply you back.
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.