SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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]

replace function sample



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

replace multiple spaces with single



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
Number of Words in a Statement

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





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems