0

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:

enter image description here

Outputs:

enter image description here

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

user1372603
  • 47
  • 1
  • 6

1 Answers1

0

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi this solution doesnt work. It returns null when I pass it anything. be it @first_name = 'Ron John' or 'am Ron John' – user1372603 Apr 08 '20 at 20:20
  • @user1372603 . . . Try with `top (1)`. – Gordon Linoff Apr 08 '20 at 20:41
  • No cigar... I updated the function in the question to the current version to avoid misunderstandings. Thank you. – user1372603 Apr 08 '20 at 21:24
  • Since its a prefix im trying to get rid of, shouldnt I be getting Right rather than Left? though changing that alone didnt help either – user1372603 Apr 08 '20 at 21:26
  • The '%' needs to be after the prefix and the function should be right not left. select Right(@name, len(@name) - (len(v.prefix) + 1)), len(v.prefix) from (values ('am'),('auf'),('auf dem'),('aus der'),('d'),('da'),('de'),('del’'),('del'),('dela'),('dele'),('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') )v(prefix) where @name like v.prefix+' %' order by len(v.prefix) desc – user1372603 Apr 09 '20 at 09:58
  • You were correct as the example in my question was incorrect. Thank you! – user1372603 Apr 09 '20 at 09:58
  • That said it still didnt work if there was no prefix as it then returned nothing. I used If Exists as per the following solution to overcome that: https://stackoverflow.com/questions/2884996/simple-check-for-select-query-empty-result – user1372603 Apr 09 '20 at 10:43