Saturday, August 7, 2021

Function Identify Special Character and Replace

 Create function in sqlserver to identify the special character in string ( in parameter), and it will replace all the special character with "AND". You can modify the function as per your requirement and can replace it with space.


DROP FUNCTION func_Gen_str

CREATE FUNCTION Func_Gen_str (@NewStr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @newstring VARCHAR(1000) = @NewStr;
	DECLARE @pattern VARCHAR(100) = '%[^"0-9a-zA-Z'' -]%';
	DECLARE @i INT;

	BEGIN
		SET @i = PATINDEX(@pattern, @newstring)

		WHILE @i <> 0
		BEGIN
			SET @newstring = LEFT(@newstring, @i - 1) + '" AND "' + SUBSTRING(@newstring, @i + 1, 1000);
			SET @i = PATINDEX(@pattern, @newstring)
		END

		RETURN @newstring;
	END;
END
GO


SELECT dbo.Func_Gen_str('a,bcde.fghifhj')

result will be 
a " AND " bcde " AND " fghifhj

No comments:

Post a Comment

web stats