?
Solved

Efficient way to compare string against multiple values

Posted on 2008-10-30
4
Medium Priority
?
912 Views
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)
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

0
Comment
Question by:bharvfor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 13

Accepted Solution

by:
joechina earned 2000 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
0
 

Author Comment

by:bharvfor
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%')
0
 
LVL 13

Expert Comment

by:joechina
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.
0
 
LVL 13

Expert Comment

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

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…
Suggested Courses

765 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