To remove or replace multiple special character from string using sql queries.

[alok pandey]

In FN_REMOVE_SPECIAL_CHARACTER] we make use of WITH clause CTE to
store the resultset of all special character in one row table.To
remove unwanted special characters,we pass string input values and
apply string’s replace funtion having special character match values
from CTE table .

CREATE FUNCTION [FN_REMOVE_SPECIAL_CHARACTER] (
@INPUT_STRING varchar(300))
RETURNS VARCHAR(300)
AS
BEGIN

–declare @testString varchar(100),
DECLARE @NEWSTRING VARCHAR(100)
– set @teststring = ‘@san?poojari(darsh)’
SET @NEWSTRING = @INPUT_STRING ;
With SPECIAL_CHARACTER as
(
SELECT ‘>’ as item
UNION ALL
SELECT ‘<' as item
UNION ALL
SELECT '(' as item
UNION ALL
SELECT ')' as item
UNION ALL
SELECT '!' as item
UNION ALL
SELECT '?' as item
UNION ALL
SELECT '@' as item
UNION ALL
SELECT '*' as item
UNION ALL
SELECT '%' as item
UNION ALL
SELECT '$' as item
)
SELECT @NEWSTRING = Replace(@NEWSTRING, ITEM, '') FROM
SPECIAL_CHARACTER
return @NEWSTRING
END
select dbo.[FN_REMOVE_SPECIAL_CHARACTER] ('@s()antosh')

[Dinesh Hona]

Thank you alok sir!

preety nice function...