Solved

Efficient way to compare string against multiple values

Posted on 2008-10-30
4
910 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 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
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

737 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