SQL Replace query to remove prefix from name contains (MR., MRS., MR / MRS.....)
sql, sql-server, sql-server-2008
Solution
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
Problem
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