Efficient way to compare string against multiple values

Posted on 2008-10-30
Last Modified: 2012-08-13
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)



     DECLARE @Return varchar(50)

     SET @Return =


			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 


			'Individual Contributor'




Open in new window

Question by:bharvfor
  • 3
LVL 13

Accepted Solution

joechina earned 500 total points
ID: 22844953
I would suggest you to create a mapping table. And query the table in your function. It's more flexible in case you want to add or modify a row.

For example,

select levelResult
from titleMappingTable
where @title like nameOfTitle

Author Comment

ID: 22845100
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%')
LVL 13

Expert Comment

ID: 22851322
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.
LVL 13

Expert Comment

ID: 22851350
Another option is to write an CLR procedure and use regular expression.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now