Solved

Efficient way to compare string against multiple values

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 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