User Rating: / 0
PoorBest 

SQL server provides a good set of functions which will perform the required string operation. But, Many times we use T-SQL to do string operation.

Let us create a User Defined Function which will return all the special characters for the specified string.



--Select dbo.udf_RemoveSpecialChars('sarath#revuri##@gmail.com')
Create FUNCTION dbo.udf_RemoveSpecialChars
	(@strValue VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @intAlpha INT
	SET @intAlpha = PatIndex('%[^A-Za-z0-9@._-]%',  @strValue)
	WHILE @intAlpha > 0
		BEGIN
		SET @strValue = STUFF(@strValue, @intAlpha, 1, '' )
		SET @intAlpha = PatIndex('%[^A-Za-z0-9@._-]%',  @strValue )
	END
RETURN ISNULL(@strValue,null)
END
GO