CODEWITHSUNDEEP
sqlsql-server
Vishwanath jawalkar
Vishwanath jawalkar

Reputation: 123

SQL SUBSTRING with Condition

I have one SQL Table with data

user_id     server_id   distinguished_name
STEVE.BROWN SERVER01    CN=Brown\, Steve N.,OU=Users,OU=CCG - US Remote (USREM),OU=fooUsers,DC=amer,DC=foo,DC=com

I want to pull the OU Server Name from above data i.e CCG - US Remote

    select SUBSTRING(distinguished_name,CHARINDEX('OU',distinguished_name),
LEN(distinguished_name)) from web_ldap_server_user where user_id='STEVE.BROWN'

I tried with this QUERY but not getting expected results. Can anyone help me here?

Upvotes: 3

Views: 998

Answers (4)

Hadi
Hadi

Reputation: 37313

try this query:

SELECT SUBSTRING(SUBSTRING(distinguished_name,CHARINDEX('OU=',distinguished_name,CHARINDEX('OU=',distinguished_name) + 1) + 
3,LEN(distinguished_name)),1,CHARINDEX(',',SUBSTRING(distinguished_name,CHARINDEX('OU=',distinguished_name,CHARINDEX('OU=',distinguished_name) + 1) + 
3,LEN(distinguished_name))) - 1) from web_ldap_server_user where user_id='STEVE.BROWN'

it return the following value:

CCG - US Remote (USREM)

Read more about String functions in Stackoverflow Documentation

Upvotes: 0

Jim
Jim

Reputation: 1

Assuming the Server name is always in the second "OU=" occurrence, then:

select SUBSTRING(r.disName, 4, charindex(',', r.disName) - 4)
    from (
    select SUBSTRING(s.disName, charindex('OU=', s.disName), LEN(s.disName)) as disName
    from (
        select SUBSTRING(distinguished_name, CHARINDEX('OU=',distinguished_name) + 3, LEN(distinguished_name)) as disName
        from web_ldap_server_user where user_id='STEVE.BROWN'
    ) s
) r

You can make it shorter but here I have it laid so you can understand the logic in steps.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

With any Split/Parse function

Select A.user_id
      ,A.server_id
      ,B.*
 From  YourTable A
 Cross Apply (Select RetSeq,RetVal=Replace(RetVal,'OU=','') 
               From  [dbo].[udf-Str-Parse](A.distinguished_name,',') 
               Where RetVal Like 'OU=%' 
             ) B

Returns

user_id       server_id   RetSeq    RetVal
STEVE.BROWN   SERVER01    3         Users
STEVE.BROWN   SERVER01    4         CCG - US Remote (USREM)
STEVE.BROWN   SERVER01    5         fooUsers

Now the question is... There are 3 "OU=" values. What logic would be required to identify the Server? For example in the CROSS APPLY I currently have Where RetVal Like 'OU=%' but this could also be Where RetVal Like 'OU=% - %'

OR if it is always the 4th position, change the WHERE to Where RetSeq=4

The UDF (which could easily be ported into the CROSS APPLY instead of a function)

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);

Upvotes: 3

Hogan
Hogan

Reputation: 70513

 CHARINDEX('OU=Users,OU=',distinguished_name)+12

Will get that location if every field has OU=Users,OU= before it.

Upvotes: 0

Related Questions