Pattern Search and Replace function–Simple SQL

I have recently got a request from my insurance client to replace the non acceptable character with space in the PolicyHolder name before sending to Employers' Liability Tracing Office (ELTO).  Also, it should allow a quick search incase of an investigation from ELTO.
I always like the wildcard search facility in the SQL server as they are quick and can avoid huge amount of coding.  E.g. let us say we want to ignore all the special character expect “@” character

   1: Create table #temp


   2: (policyholdername varchar(30))


   3:  


   4: insert into #temp values ('Nishar/123')


   5: insert into #temp values('Nishar@123')


   6:  


   7: select * from #temp where policyholdername not like '%[^@a-zA-Z1-9]%'


The above code will only pick the second row and delivers it out of the box.  But unfortunately SQL server doesn’t give a Pattern Replace function out of the box so the only option is to create an user defined function. 

Below function has been developed with below goals in mind

• Create a function which should be generic
• uses the natural SQL functions rather than costly cursors or loops.

   1: ALTER FUNCTION PatReplace ( @sInput VARCHAR( max),@NonAllowedPattern varchar(max ),@ReplaceWith Varchar (1))

   2: RETURNS VARCHAR (max)

   3: AS

   4: BEGIN

   5: ---- =============================================

   6: ---- Author:            <Mohamed Nishar>

   7: ---- Create date: <05/06/14>

   8: ---- Description: Replace all characters matching with NonAllowedPattern based on supplied replacewith character

   9: ---- =============================================

  10:        DECLARE @Clenoutput AS VARCHAR( max);

  11:  

  12:        WITH CTE_patreplace

  13:        AS (

  14:        -- Check for any non allowed character and repalce it with new cahracter specified

  15:              SELECT CASE

  16:                          WHEN substring (@sInput, N, 1 ) LIKE @NonAllowedpattern

  17:                                THEN @ReplaceWith

  18:                          ELSE substring (@sInput, N, 1 )

  19:                          END sPolicyHolderNameCleanChar

  20:              FROM

  21:              -- A dummy table which generates long list of key numbers to strip the supplied string into individual character set

  22:              (

  23:                    SELECT row_number () OVER (

  24:                                ORDER BY sc1.NAME

  25:                                ) AS n

  26:                    FROM master .dbo. syscolumns sc1

  27:                          ,master. dbo.syscolumns sc2

  28:                    ) AS tal

  29:              WHERE n <= LEN( @sInput)

  30:              )

  31:        SELECT @Clenoutput = (

  32: -- FOR XML is used here to combine multiple rows (list of character from previous query) into single value

  33:                    SELECT isnull (stuff((

  34:                                            (

  35:                                                  SELECT '' + sPolicyHolderNameCleanChar

  36:                                                  FROM CTE_patreplace

  37:                                                  FOR XML PATH( '')

  38:                                                        ,root( 'MyString')

  39:                                                        ,type

  40:                                                  ).value( '/MyString[1]', 'varchar(max)')

  41:                                            ), 1, 0, ''), '')

  42:                    )

  43:  

  44:        RETURN @Clenoutput

  45: END

Happy reading