Tuesday 14 April 2015

Way to replace individual characters from the string

In one of the project that I am associated with, we had a requirement to replace each occurrence of the characters from the input string by the characters of users choice.

For example -

Suppose we have a string - "Chetan Deshpande" 
and we have been asked to
  • Replace D  by C
  • Replace E  by O
  • Replace S  by U
  • Replace H  by N


As we all know in SQL server we have a REPLACE() function to do this task. But the requirement that we had was bit different so nesting of replace function is required here

This can be achieved using Replace with following command

SELECT REPLACE(REPLACE(REPLACE(REPLACE('CHETAN DESHPANDE','D','C'),'E','O'),'S','U'),'H','N')

What if the list of characters that needs to be replaced is maximum i.e. we have 26 such combinations to address then it is quite cumbersome to have 26 levels of nesting using REPLACE(). 
Instead why not write a SQL function which would do the job. This triggered me to write a small function which would do this for me


CREATE FUNCTION [dbo].[udf_ReplaceEachChar]
(
@InputString VARCHAR(8000),
@CharToBeReplaced VARCHAR(26),
@ReplacedByChar VARCHAR(26)
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @OUTPUT VARCHAR(8000)

;WITH CTE AS
(
SELECT @InputString InputString,@CharToBeReplaced CHARToBeReplaced,@ReplacedByChar ReplacedByChar,
SUBSTRING(@CharToBeReplaced,1,1) T_R,SUBSTRING(@ReplacedByChar,1,1) W_R,1 AS ID,
CONVERT(VARCHAR(1000),@InputString) AS REPLACED

UNION ALL

SELECT InputString,CHARToBeReplaced,ReplacedByChar,SUBSTRING(CHARToBeReplaced,ID,1),
SUBSTRING(ReplacedByChar,ID,1),ID + 1,CONVERT(VARCHAR(1000),REPLACE(REPLACED,T_R,W_R))
FROM CTE WHERE T_R <> ''

)
SELECT @OUTPUT = REPLACED
FROM CTE 
WHERE T_R = '' AND W_R=''

RETURN(@OUTPUT)

END

GO

SELECT DBO.[udf_ReplaceEachChar]('CHETAN DESHPANDE','DESH','COUN')

No comments:

Post a Comment

bloggerwidgets