How to remove the special characters from the string in the SQL Server

Step 1:- Before starting to know this we have to learn one function the Sql server

PATINDEX()

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types

Example 1 Write the below sentences

SELECT PATINDEX('%lokeshasp%', 'Have You ever seen this lokeshasp.blogspot.com ');

--This is also used for the first get the char index and the word index

OutPut:-

25

Example 2

DECLARE @GetIndex INT,@string varchar(100)

SET @string='welcome-to''lokeshasp.blogspot.com#$'

SET @GetIndex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)

WHILE @GetIndex > 0

BEGIN

SET @string = STUFF(@string, @GetIndex, 1, ' ' )

SET @GetIndex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)

END

SELECT @string

OutPut :-

welcome to lokeshasp blogspot com