Sql-server – Matching substring to table column

sql serversql-server-2008-r2t-sql

I have a mapping table which defines access. 

   Us | Them
    ----------
    1    MA
    2    LA
    3    NA
    4    MA

Customers send in their role and we return our role which represents access inside our system.
A simple example is

  • MA = Master access
  • LA = Limited access
  • NA = No access
  • SA = Some access

We now will be receiving a list of roles from one of our customers,
this will be in the form of "MA;LA;NA" or "LA;NA" or even "LA;XX"

What I need to do is return the highest role back to them.
SO if it exists in our table a users role becomes the highest possible access. Input is "LA;MA" I return 1.
Input is "NA;LA" I return 2.
Input is "does not exist" I return 3.

Again this is a simple example they have of 60 roles defined.
My question is what is a clean way of doing this?

I have a working solution which has a series of IF's and ELSE IF's where if the inputted string is like "MA" it represents a 1 and move on. However the code is rather verbose and pretty ugly, order matters and if the customer requests a new role say "Orange" and we insert it into the mapping table, I then would have to edit the stored procedure.

Any thoughts to point me in the right direction?

Best Answer

I will suggest to have a table valued function. This function will accept your string as an input parameter and then return a table by splitting the inputs separated by ";" in different rows with an index ID. You can use this function internally with you logic and determine the MAX(Us) from mapping table. Below is the sample example for the function.

CREATE FUNCTION [dbo].[funcSplit] ( @string VARCHAR(MAX) )
RETURNS
 @List TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @string ) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @string )
  SELECT @name = SUBSTRING(@string , 1, @pos-1)

  INSERT INTO @List
  SELECT @name

  SELECT @string = SUBSTRING(@string , @pos+1, LEN(@string )-@pos)
 END

 INSERT INTO @List
 SELECT @string 

 RETURN
END

Once implemented, use this function as below in your own logic to determine the roles.

SELECT MAX(us)
FROM YourMappingTable AS A
     INNER JOIN funcSplit('MA;LA') AS B
     ON A.Them = B.Name

Only thing is remaining is to include an exception for the NA or "Input Does not exists" case which can be easily handled.

Related Question