Remove special characters from string in SQL server


I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solutions is available but T-SQL is quickly resolved this issue.

Following query may help you to resolve your issue.


DECLARE @str VARCHAR(400)
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'
SET @str = '(remove) ~special~ *characters. from string in sql!'
  WHILE PATINDEX( @expres, @str ) > 0
        BEGIN
         SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
        END
SELECT @str




Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...