SQL server SPLIT String

Question

Can someone help me with a function to get a part of the text from this string?

L=CTV,OU=CALUBCO,OU=testext,DC=Test,DC=com  

I need the value OU=CALUBCO from the text above.

Thanks in advance!

Answer 1

Hi @Santhosh Kunder ,

Please try the following solution based on tokenization via JSON.
It will work starting from SQL Server 2016 onwards.

We are looking for the 2nd token in the comma separated list of tokens.
JSON array starts from 0 (zero).

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (tokens VARCHAR(1000));  
INSERT INTO @tbl (tokens) VALUES   
 ('L=CTV,OU=CALUBCO,OU=testext,DC=Test,DC=com');  
-- DDL and sample data population, end  
   
SELECT Result = JSON_VALUE(S,'$[1]')  
FROM @tbl  
CROSS APPLY (VALUES ('["' + REPLACE(STRING_ESCAPE(tokens,'json'),',','","') + '"]')) AS B(S);  

Output

+------------+  
|   Result   |  
+------------+  
| OU=CALUBCO |  
+------------+  

Answer 2

 /* string_split in SQL 2022  */  
 DECLARE @str varchar(200)='L=CTV,OU=CALUBCO,OU=testext,DC=Test,DC=com'   
    
 select * from  string_split(@str,',',1)  
 Where ordinal=2  

Answer 3

Hi @Santhosh Kunder ,

select SUBSTRING('L=CTV,OU=CALUBCO,OU=ShellExternal,DC=shell,DC=com',CHARINDEX(',',' L=CTV,OU=CALUBCO,OU=ShellExternal,DC=shell,DC=com'),10)  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.