Technical Article

Replace-Function

,

This is my little Replace-trick, just because I dont like the Replace(Replase(Replace(Replace string I've seen in a bunch of stored procedure and functions. First off all you should have a Codebase where you can insert all of your functions, mine is called Codesnippets but you can call i wathever you want.

/*
This is my little Replace-trick, just because I dont like the Replace(Replase(Replace(Replace string ive seen in a bunch of stored procedure and functions.
First off all you should have a Codebase where you can insert all of your functions, mine is called Codesnippets but you can call i wathever you want.
Install the Replace function in 3 steps:
1. Create the Replace table, mine is called Synonyms
USE [Codesnippets]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Synonyms](
[synonym] [varchar](50) NULL,
[word] [varchar](50) NULL
) ON [PRIMARY]
GO

2. Populate the data into the synonyms-table
Example
Synonym = "Hi"Word = "Hello"
Synonym = "One"Word = "1" and so on
The Synonym is the word you want to replace with the word in word.
3. Create the function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_ReplaceChars]
(
@StringVARCHAR(max)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Synonyms table (synonym VARCHAR(50),word VARCHAR(50))
INSERT INTO @Synonyms
SELECT synonym, word FROM [dbo].[Synonyms]
DECLARE @synonym VARCHAR(50)
DECLARE @word VARCHAR(50)
DECLARE @iRadnr INT = 1
WHILE @iRadnr > 0 
BEGIN
SELECT TOP 1 @synonym = [synonym], @word = [word] FROM @Synonyms
SET @iRadnr = @@ROWCOUNT
SET @String = REPLACE(@String,@synonym,@word)
DELETE FROM @Synonyms WHERE [word]= @word AND  [synonym] = @synonym 
END
RETURN @String
END
GO
*/

Rate

1.6 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

1.6 (5)

You rated this post out of 5. Change rating