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
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.