SQL Server pass in string value and see if it is in a hard-coded list of values and return int

Question

I have a stored procedure that I have created that for right now I am not touching a database.

Pseudo code

CREATE PROCEDURE [dbo].[CustomerKeyChecker]
   @ldccode VARCHAR(12)
AS
   @result int = 0
BEGIN
    DECLARE @listofCodes varchar(200)
    SET @listofCodes = 'CLP, UIC, NSTAR, NSTARB, NSTARC, PSNH'

    -- So lets say that the passed in @ldccode is "CLP" , well then I went to   
    -- set a   @result = 1  

END

What is the a decent way to do a substring to search for these codes inside list?

Pseudo code:

 substring(@lcdcode, @listOfCodes)    ?

Again, for now this is nothing to do with the database, and I understand that someone would say "why even pass this data to sql" ... there will be sql tables added in later is why ...

Kindly refer http://stackoverflow.com/a/2657/1460189 – SuicideSheep Apr 11 '17 at 01:44 — SuicideSheep, Apr 11 '17 at 01:44

John Cappelletti · Accepted Answer · 2017-04-11T02:39:54.790

You may notice that we add a space to the begining and comma to the end of each variable. This is to prevent false positives i.e. STAR. You may also notice SIGN(), this will return 1 if a positive number, 0 if not found.

Declare @ldccode VARCHAR(12) = 'CLP'
Declare @result int = 0

declare @listofCodes varchar(200)
SET @listofCodes = 'CLP, UIC, NSTAR, NSTARB, NSTARC, PSNH'

Select @result=sign(charindex(' '+@ldccode+',',' '+@listofCodes+','))

Returns

SQL Server pass in string value and see if it is in a hard-coded list of values and return int

1 Answers1