How do i get sub string from string in between two symbol

Question

I want to get sub string in between two operators like = and ,

I tried something like CHARINDEX and LEFT to get the value but i got output in terms of CN=Khushwant Khatri but my output should be only Khushwant Khatri

SELECT left([String_value],CHARINDEX(',',([String_value]),0)-1) from trim_string

My string look like

CN=Khushwant Khatri,OU=TestMig,DC=valjha,DC=vedantaresource,DC=local

CN=Raghav Tare,OU=EXECUTIVE,OU=EXUDR,DC=HZL01,DC=vedantaresource,DC=local

CN=D K Chodankar,OU=Users,OU=AD LotusSync,DC=SGL01,DC=vedantaresource,DC=local

as you can see string has variable length

i want only CN value my output should look like Khushwant Khatri Raghav Tare D K Chodankar

Answer 1

You may try this. First you need to find the position of your first =, since name will start from there, then need to find the length of your name which is separated by ,. So we find the index of next , and substract it from the length of string till =. Remaining string is your name as expected.

I am considering that your first value 'CN=' may vary for some condition.

declare @str varchar(max) = 'CN=Khushwant Khatri,OU=TestMig,DC=valjha,DC=vedantaresource,DC=local'

select substring( @str, charindex('=',@str)+1, (charindex(',',@str) - (charindex('=',@str) +1) )) 

As per given table structure details please find the below code snippet.

Create table trim_string ( string_value nvarchar(max) )

Insert into trim_string ( string_value )
values ( 'CN=Khushwant Khatri,OU=TestMig,DC=valjha,DC=vedantaresource,DC=local' )
, ( 'CN=Raghav Tare,OU=EXECUTIVE,OU=EXUDR,DC=HZL01,DC=vedantaresource,DC=local' )
, ( 'CN=D K Chodankar,OU=Users,OU=AD LotusSync,DC=SGL01,DC=vedantaresource,DC=local' )

----- this is your query section
; with cte as (
select string_value ,  substring( string_value, charindex('=',string_value)+1, (charindex(',',string_value) - (charindex('=',string_value) +1) )) newvalue  
 from trim_string )
---- you may store them in temptable
select * into #temp from cte
----or
---- for selection 
select * from cte
----or
-----  use to update
update cte set string_value = newvalue

select * from trim_string

As per the comments discussion please try this.

; with cte as (
select string_value ,  substring( string_value, charindex('=',string_value)+1, (charindex(',',string_value) - (charindex('=',string_value) +1) )) newvalue  
 from trim_string )
select * from cte

This should give you all of the strings record of your table, with Name is fetched in new column as newvalue.

Answer 2

Use SUBSTRING() as

SELECT SUBSTRING(S, 4, CHARINDEX(',', S)-4),
       S
FROM
(
  VALUES
  ('CN=Khushwant Khatri,OU=TestMig,DC=valjha,DC=vedantaresource,DC=local'),
  ('CN=Raghav Tare,OU=EXECUTIVE,OU=EXUDR,DC=HZL01,DC=vedantaresource,DC=local'),
  ('CN=D K Chodankar,OU=Users,OU=AD LotusSync,DC=SGL01,DC=vedantaresource,DC=local')
) T(S);