Solved

Efficient way to compare string against multiple values

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 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