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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

12 Experts available now in Live!

Get 1:1 Help Now