Link to home
Start Free TrialLog in
Avatar of bharvfor
bharvfor

asked on

Efficient way to compare string against multiple values

I'm looking for a more efficient way to write the following function.  It seems to perform ok but it will be nasty to maintain.  Is there a best practice in this type of scenario?
CREATE FUNCTION [dbo].[DecisionLevel] (@Title varchar(100))
RETURNS varchar(50)
AS
BEGIN
     DECLARE @Return varchar(50)
     SET @Return =
		Case 
			When @title like '%CEO%'
				or @title like '%CMO%'
				or @title like '%CTO %'
				or @title like '%CIO%'
				or @title like '%CFO%'
				or @title like '%Chief%'
				or @title like '%General Mgr%'
				or @title like '%General Mana%'
				or @title like '%Preside%'
				or @title like '%Executive%'
				or @title like '%EVP%'
				or @title like '%Head%'
				or @title like '%Leader%'
				or @title like '%Global%' 
				or @title like '%Chairman%'
						Then 'Executive (C-Level)'
			When @title like '%VP%' 
				or @title like '%V.P%' 
				or @title Like '%Vice%'
						Then 'Vice President'
			When @title like '%Director%' 
				or (@title like '%dir%' or @title like '%dir.%') and (@title Not like '%direct m%')
				or @title like '%leiter%' 
						Then 'Director'
			When @title like '%mgr%' 
				or @title like '%manager%' 
				or @title like '%Abteilungsleiter%' 
				or @title like '%chef de%' 
						Then 'Manager'
			When IsNull(@title,'N') = 'N' Then Null 
		Else
			'Individual Contributor'
		End
     RETURN(@return)
END;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of joechina
joechina

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bharvfor
bharvfor

ASKER

Thanks - How would I handle:
- Cases where multiple levels could be returned.  Since the case expression exits when the condition is met I only get one value and I can process sequentially to set my rules of precedence.
- Logical conditions:  i.e. --(@title like '%dir%' or @title like '%dir.%') and (@title Not like '%direct m%')
You can have an order column in your mapping table.

for example,

%dir%   3
%direct m%  4
%Preside% 1
%Vise Preside% 2

and order by the result descending and pick the first one.
Another option is to write an CLR procedure and use regular expression.