Removing Suffixes from Names

[AlexCuse]

Hi All,

I grew tired of typing this code into my queries (after the second time!), so I created a function today to trim suffixes off of last name and full name fields. I am sure there are better ways to do this, but this will help me tremendously. Hopefully it will save someone else out there the pain I went through.

Create  Function fnNameTrim(@name varchar (100))
Returns varchar(100)
AS
Begin

Declare @fixname varchar(100)

set @fixname =

(CASE WHEN right((@name),5) in (', Jr.', ', Sr.', ', III', ', 3rd') then
ltrim(left(@name, len(@name) - 5))
WHEN right(@name,4) in (' Jr.', ' Sr.', ' III', ' 3rd', ', II', ', IV', ', MA', ', MD') then
ltrim(left(@name, len(@name) - 4))
WHEN right(@name,3) in (' Jr', ' Sr',  ' II', ' IV', ' I.', ' J.') then
ltrim(left(@name, len(@name) - 3))
WHEN right(@name,1) = '.' and right(@name, 3) NOT IN ('Jr.', 'Sr.') then
ltrim(left(@name, len(@name) - 1))
ELSE (@name) END) 

return ltrim(rtrim(@fixname))

End

If anyone has a better way, I am all ears!

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[SQLSister]

You might also consider adding MD and M.D. and the variations of PHD to your list. I don't know if they ever come up in your dat but they dio in mine all the time.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mmerlinn]

I have absolutely no knowledge of SQL, but I would do this in FoxPro like the following.

[tt]
FUNCTION NameTrim
PARAMETERS qName
PRIVATE ALL LIKE z*

&& MUST be UPPER case
zlist = 'JR,SR,III,IV,3RD,MA,MD,PHD,I,J,'

&& Remove all dots
zname = STRTRAN(qName, '.', '')

&& Find location of last space
zindex = RAT(' ', zname)

&& In junk list???
&& If so, then remove junk
IF(UPPER(SUBSTR(zname, zindex + 1))) $ zlist
zname = LEFT(zname, zindex - 1)
ENDIF

&& Remove all trailing non-alpha characters
&& Assumes name has at least one alpha character
DO WHILE NOT ISALPHA(RIGHT(zname, 1))
zname = LEFT(zname, LEN(zname) - 1)
ENDDO

RETURN ALLTRIM(zname)
[/tt]

This removes all dots, all suffixes in list, all trailing non-alpha characters, and all leading spaces while leaving the rest of the name unchanged.

Best part is that it is easily updateable with other suffixes which can be added or removed as needed without changing anything other than the list of junk suffixes.

Two critical assumptions are made. First that the name does NOT contain ONLY a junk suffix. And second that the resulting name will be AT LEAST one alpha character long. A third assumption made is that no name should have ANY dots in it.

Hope this gives you some ideas on how to make your function better.




mmerlinn

"Political correctness is the BADGE of a COWARD!"