Extract values from the string

Question

Hi All,

I have one Input string parameter in the stored procedure of type nvarchar(max)

'10=1,11=0,12=1,13=0'
In the string 10=1 is nothing but Server ID = Status. So server 10 is enabled , server 11 is disabled etc

Now, I want to extract these values from this string for all 4 servers and insert into my table

ServerInfoDetails (ServerID, Status)

Could you please help me understand how can I extract values?

Answer 1

Hi @Rishabh Patil ,

Please try the following solution.

SQL

DECLARE @ServerInfoDetails TABLE (ServerID INT PRIMARY KEY, Status BIT);  
  
DECLARE @param nvarchar(max) = '10=1,11=0,12=1,13=0';  
  
INSERT INTO @ServerInfoDetails (ServerID, Status)  
SELECT ServerID = LEFT(value, pos - 1)  
 , Status = RIGHT(value, LEN(value) - pos)  
FROM STRING_SPLIT(@param, ',')  
 CROSS APPLY (SELECT CHARINDEX('=', value)) AS t(pos);  

-- test  
SELECT * FROM @ServerInfoDetails;  

Output

+----------+--------+  
| ServerID | Status |  
+----------+--------+  
|       10 |      1 |  
|       11 |      0 |  
|       12 |      1 |  
|       13 |      0 |  
+----------+--------+  

Answer 2

Use JSON with YitzhakKhabinsky-0887's sample:
DECLARE @ServerInfoDetails TABLE (ServerID INT PRIMARY KEY, Status BIT);

 DECLARE @param nvarchar(max) = '10=1,11=0,12=1,13=0';  
  
 --select * from  openjson('{"'+Replace(Replace(@param, '=','":"'),',','","') +'"}')  
   
      
 INSERT INTO @ServerInfoDetails (ServerID, Status)   
  
 select [key],value   
 from  openjson('{"'+Replace(Replace(@param, '=','":"'),',','","') +'"}')  
   
 SELECT * FROM @ServerInfoDetails;