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, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2016
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
  SELECT @string = REPLACE(@string, @search_string, @replace_string)

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
LEN(@temp_string) - LEN(REPLACE(@temp_string, SPACE(1), '')) + 1
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)

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
SELECT @string = REPLACE(@string, @search_string, @replace_string)

RETURN @string



Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems