REPLACE SPECIAL CHARACTER FROM STRING IN SQL

• In my project, I have one .csv file and I load csv file into SQL server tables where SQL server table column have some time NVARCHAR(which include special character) columns

• Some time it got some special character in my table column (ex. In my alphanumeric employee code have ‘#’ or ‘?/’), I need to replace all special character with no space

•  I have option with replace function to remove unwanted space but it seems I need write series of replace function.

SELECT REPLACE( REPLACE( REPLACE( REPLACE(@str, '/', '' ), '#', '' ));

•  I need to create dynamic function which replaces all special character with no space.

      DECLARE @str VARCHAR(25)

      SET @str = '(NIL)AY-MISTRY''l123'

     SELECT PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str )

     WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0

    SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX(               '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')

     SELECT @str

• Above query we can also convert into SQL function to return no space string value.

• More about PATINDEX (Ref. Link)

  http://technet.microsoft.com/en-us/library/ms188395%28v=sql.105%29.aspx