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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
- 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%')