REPLACE T-SQL String Function
REPLACE t-sql string function can be used to replace all occurences of a specified string value in an other given string value.
Replace function takes 3 parameters; first parameter is the string or binary data where replacement process will take place over.
Second parameter is the substring which searched for in the first parameter.
Third and the last parameter is again in string or binary data type and will replace the the second parameter in the first given parameter.
REPLACE(main_string, search_string, replace_string)
If any of the three parameters is NULL then the REPLACE function will return NULL value also.
T-SQL Replace Function Sample Scripts and Codes
SELECT REPLACE(N'Hi, my book is in my bag.', 'my', 'your') AS [Replaced String Value]
Real-Life Sample Case: Replace Space Characters Following Each Other with One Space Character
It is sometimes necessary in your daily tasks to replace space characters following each other with a single space character.
This seems to be simply a formatting task. But it is not easy to manage this task if you have to do it using t-sql commands.
Here is given a sample solution you can apply to similar tasks or similar sql problems.
DECLARE @string AS nvarchar(max)
DECLARE @search_string AS nvarchar(max)
DECLARE @replace_string AS nvarchar(max)
SET @string = N'This statement has more than one space characters between each word.'
SET @search_string = SPACE(2)
SET @replace_string = SPACE(1)
WHILE REPLACE(@string, @search_string, @replace_string) <> @string
BEGIN
SELECT @string = REPLACE(@string, @search_string, @replace_string)
END
SELECT @string
Real-Life Sample Case: Count Number of Words in a Statement using T-SQL
Here is a sample solution for a real life problem which SQL Server developers are generally experiencing.
Many sql programmers have faced the problem and the difficulty of counting the number of words in an character variable or in a string type column.
Here is a simple solution which needs to be further developed, but I believe the t-sql sample code given here might help you in the solution of similar daily life problems.
DECLARE @string AS nvarchar(max)
DECLARE @temp_string AS nvarchar(max)
SET @string = N' This is a sample statement which we are trying to count its words...The count is 0!!!... '
SELECT @temp_string = LTRIM(RTRIM(dbo.ReplaceMultiSpaces(REPLACE(REPLACE(@string,'.', SPACE(1)),'!', SPACE(1)))))
SELECT [Number Of Words] =
CASE WHEN LEN(@temp_string) = 0
THEN 0
ELSE
LEN(@temp_string) - LEN(REPLACE(@temp_string, SPACE(1), '')) + 1
END
And the t-sql source code for the ReplaceMultiSpaces user-defined function is given below:
CREATE Function ReplaceMultiSpaces
(
@string AS nvarchar(max)
)
RETURNS nvarchar(max)
BEGIN
DECLARE @search_string AS nvarchar(max)
DECLARE @replace_string AS nvarchar(max)
SET @search_string = SPACE(2)
SET @replace_string = SPACE(1)
WHILE REPLACE(@string, @search_string, @replace_string) <> @string
BEGIN
SELECT @string = REPLACE(@string, @search_string, @replace_string)
END
RETURN @string
END
GO
SQL UDF Word Count Function for Database Programmer
This SQL script can be converted into a User-Defined Function by SQL Server database developers using below T-SQL command so that the Word Count function "WordCountFunction" can be used repetitive use by SQL programmers.
Please note that I used "Create or Alter Function" command which is new with SQL Server 2016 SP1 as a new database development enhancement.
Create or Alter Function WordCountFunction (
@string AS nvarchar(max)
)
returns int
as
begin
DECLARE @temp_string AS nvarchar(max)
SET @temp_string = LTRIM(RTRIM(
dbo.ReplaceMultiSpaces(REPLACE(REPLACE(@string,'.', SPACE(1)),'!', SPACE(1)))
));
RETURN (
SELECT
CASE WHEN LEN(@temp_string) = 0
THEN 0
ELSE
LEN(@temp_string) - LEN(REPLACE(@temp_string, SPACE(1), '')) + 1
END
) -- Number Of Words
end;
GO
And this SQL function can be used to count the words in the provided input string variable as follows:
select dbo.WordCountFunction('Hi! My name is Eralper.');
Of course, it is also possible to apply this SQL UDF scalar function over a table column as follows:
select description, dbo.WordCountFunction(description) from sys.configurations
