Query string_split()-CodePudding

I am trying to iterate over a long string that I will pass to a stored procedure in SQL Server that will contain "State" plus "Id" separated by comma - like so

string = "NH!01,MA!02,VA!04"

My question is, how can I select items from a table that has 3 columns state, id and descr, based on that list of valises on the string above?

select * 
from tablename 
where state = first item[0] in the list 
  and id = first item[1] in the list
  and so on.

Please feel free to suggest better implementation for this.

CodePudding user response：

Why not a simple JOIN on the parsed results

Declare @S varchar(max) = 'NH!01,MA!02,VA!04'

Select A.*
 From  YourTable A
 Join (
        Select ST=left(value,2)
              ,ID=substring(value,4,25)
         From string_split(@S,',')
      ) B on A.[State]=B.ST      
         and A.[ID]   =B.ID