June 28, 2014

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 Smile

No comments:

Post a Comment