How to use a function remove multiple possible prefixes from a Name string in SQL Server

I need to correct Names of users by removing prefixes for a report. I need to use a function here since there is an elaborate case logic in the report. I want to pass a first name with prefix and get back a first name without a prefix. I will be applying the function on every row of the report as part of the select.

For example, my list of prefixes is:

am, auf, auf dem, aus der, d, da, de, de l’, del, de la, de le, di, do, dos, du, 
im, la, le, mac, mc, mhac, mhíc, mhic giolla, mic, ni, ní, níc, o, ó, 
ua, ui, uí, van, van de, van den, van der, vom, von, von dem, von den, von der

I want to remove any of these prefixes from the First Name if they are present.

For example – inputs:

Outputs:

I know I can take a brute force approach and do a replace 40 odd times, but was wondering if there is a better/smarter way to do this using a function, given the list of names in the report can be in the tens of thousands, daily.

I created this function based on an answer to another question but its not working for me:

alter FUNCTION [dbo].[fn_RemoveNamePrefix](@name varchar(100))

RETURNS varchar(100) AS BEGIN

DECLARE @name_without_prefix varchar(100)

select top (1) @name_without_prefix = left(@name, len(@name) – len(v.prefix) – 1)

from (values (‘am’),(‘auf’),(‘am’), (‘auf’),(‘aufdem’),(‘ausder’),(‘d’),(‘da’), (‘de’),(‘del’’),(‘del’),(‘dela’),(‘dele’),(‘di’),(‘do’),(‘dos’),(‘du’),(‘im’), (‘la’),(‘le’),(‘mac’),(‘mc’),(‘mhac’),(‘mhíc’),(‘mhicgiolla’),(‘mic’),(‘ni’),(‘ní’),(‘níc’),(‘o’),(‘ó’),(‘ua’),(‘ui’),(‘uí’),(‘van’),(‘vande’),(‘vanden’),(‘vander’), (‘vom’),(‘von’),(‘vondem’),(‘vonden’),(‘vonder’) )v(prefix)

where @name like ‘% ‘ + v.prefix

order by len(v.prefix) desc

return @name_without_prefix

END

Thank you

Answer

I think you want:

select top (1) @first_name_without_prefix = left(@first_name, len(@first_name) - len(v.prefix) - 1)
from (values ('am'), ('auf')
     ) v(prefix)
where @first_name like '% ' + v.prefix
order by len(v.prefix) desc

Advertisement

Answer