SQL Replace query to remove prefix from name contains (MR., MRS., MR / MRS.....)

Question

i have a table of content like this

tblEmployees

employeeID       employeeName
___________________________________
    1       Jeffrey L. JR Van Hoosear
    2       DAVID GUNGNER MR
    3       CATHLEEN E STADECKER MRS.
    4       MARTIN W SCHIFFMILLER
    5       JAY F MOLDOVANYI VI

and Another table like this

tblPrefix

prefixID       Prefix
_________________________
    1           JR
    2           MR
    3           MR / MRS
    4           JR.
    5           MRS.
    6           I
    7           II
    8           III
    9           IV
   10           V
   11           VI
   12           VII

Now i would like to remove prefix (JR, JR., MR, MRS.....) present in EmployeeName.

i have written a function. For that function i have passed Employee name as parameter like this

SELECT * FROM fn_SplitName (@employeeName) (Table-Valued Function)

and i tried like this

SELECT REPLACE(@employeeName,preFix,'') 
FROM tblPrefix 
WHERE @employeeName LIKE '% ' + preFix + ' %'

expected outPut

employeeID       employeeName
___________________________________
    1       Jeffrey L. Van Hoosear
    2       DAVID GUNGNER
    3       CATHLEEN E STADECKER
    4       MARTIN W SCHIFFMILLER
    5       JAY F MOLDOVANYI

Compare with first tblEmpoyee

Answer 1

Oracle Query:

select employeeName, REPLACE(employeeName, PREFIX,'')  
from employee_table, prefix_table
WHERE INSTR(employeeName, PREFIX) > 0

In SQL-Server, I think it should be:

select employeeName, REPLACE(employeeName, PREFIX,'')  
from employee_table, prefix_table
WHERE CHARINDEX(PREFIX,employeeName) > 0

Answer 2

The following query selects employeeNames that start or end with a prefix. Then, the prefix is stripped off the employeeName using the SUBSTRING function.

EDIT: Corrected the CASE statement.

SELECT te.employeeName, 
    CASE
        WHEN te.employeeName like '%'+' '+tp.Prefix THEN SUBSTRING(te.employeeName, 1, LEN(te.employeeName)-LEN(tp.Prefix)-1)
        WHEN te.employeeName like tp.Prefix+' '+'%' THEN SUBSTRING(te.employeeName, LEN(tp.Prefix)+2, LEN(te.employeeName)-LEN(tp.Prefix)-1)
    END employeeName_without_Prefix
FROM tblEmployees te
INNER JOIN tblPrefix tp ON te.employeeName like '%'+' '+tp.Prefix OR te.employeeName like tp.Prefix+' '+'%';

The above query would not unintentionally replace prefix characters that occur in the middle of the employeeName.

SQL Fiddle demo

You can embed the SQL statement in a function, as below. However, please note that the function would perform slower, as it is executed for each employeeName one by one.

CREATE FUNCTION dbo.remove_prefix (@employeeName varchar(100))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @employeeName_without_Prefix   varchar(100)

SELECT @employeeName_without_Prefix = 
    CASE
        WHEN te.employeeName like '%'+' '+tp.Prefix THEN SUBSTRING(te.employeeName, 1, LEN(te.employeeName)-LEN(tp.Prefix)-1)
        WHEN te.employeeName like tp.Prefix+' '+'%' THEN SUBSTRING(te.employeeName, LEN(tp.Prefix)+2, LEN(te.employeeName)-LEN(tp.Prefix)-1)
    END employeeName_without_Prefix
FROM tblEmployees te
INNER JOIN tblPrefix tp ON te.employeeName like '%'+' '+tp.Prefix OR te.employeeName like tp.Prefix+' '+'%';
     RETURN (@employeeName_without_Prefix);
END;

Reference:

Create Function on MSDN