To swap characters

Posted by VIJI Friday, July 3, 2009

About this SQL User defined function

This Function will help SWAP characters in a string
/*This function accepts string as a input parameter and positions of the characters to be swaped*/
/*It Return the swapped string*/

CREATE FUNCTION dbo.FuncCharSwap
(
@fullstring VARCHAR(1000),
@charlocation1 TINYINT,
@charlocation2 TINYINT
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @returnval varchar(1000)

DECLARE @begin VARCHAR(1000), @middle VARCHAR(1000), @end VARCHAR(1000)
DECLARE @firstchar CHAR(1), @secondchar CHAR(1), @len INT

SET @fullstring = LTRIM(RTRIM(@fullstring))
SET @len = LEN(@fullstring)

IF @charlocation1 > @len OR @charlocation2 > @len
SET @returnval = @fullstring
ELSE
BEGIN
SET @firstchar = SUBSTRING(@fullstring, @charlocation1, 1)
SET @secondchar = SUBSTRING(@fullstring, @charlocation2, 1)
SET @begin = LEFT(@fullstring, (@charlocation1-1))
SET @middle = SUBSTRING(@fullstring, @charlocation1+1, (@charlocation2-@charlocation1)-1)
SET @end = SUBSTRING(@fullstring, @charlocation2+1, @len)
SET @returnval = @begin + @secondchar + @middle + @firstchar + @end
END

RETURN(@returnval)
END


SAMPLE USAGE
SELECT EmpName, dbo.FuncCharSwap(EmpName, 1, 3) AS [RESULT]
FROM dbo.Employee

EmpName
Venki
sekhar

RESULT
neVki
keshar

0 Responses to To swap characters

Post a Comment