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 derI 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
Advertisement
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.prefixorder by len(v.prefix) desc