Selecting Certain Part of a string

  • DECLARE-NOOB

    SSC Enthusiast

    Points: 126

    Good afternoon all,

    Hopefully you can help, I will admit firstly however that I know barely nothing about SQL. 🙂

    Basically I am trying to get names from a string, however I need to remove the first few characters from the left, this is fine as it is always the same amount of characters(CN=), however I also need to remove from the right, I have tried doing this with CHARINDEX however getting a little stuck as the number of characters can change.

    I have tried various things like using substring etc but as I am completely new to SQL getting a little stuck

    CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk

     

    Hopefully you can help 🙂

     

    Thanks

  • scdecade

    SSChasing Mays

    Points: 654

    The safest way to do this imo is to look for the first occurrence of ',OU=' in a case-sensitive way.

    declare @test		nvarchar(max)=N'CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk';

    select substring(@test, 4, charindex(N',OU=' collate Latin1_General_CS_AS, @test, 4)-4) some_name;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • DECLARE-NOOB

    SSC Enthusiast

    Points: 126

    Hi ScDecade thanks for the reply,

    Thanks for your reply, just to check, if I declare the @test variable with the example above, I assume that will just reference that string.

    The actual CN values can differ quite substantially, can I just reference the Column name in the variable?

     

    Thanks

  • scdecade

    SSChasing Mays

    Points: 654

    Give it a try and see!  🙂  Yes it should work like:

    select
    substring(@test, 4, charindex(N',OU=' collate Latin1_General_CS_AS, st.some_column, 4)-4) some_name
    from
    some_table st;

    • This reply was modified 1 month ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Eirikur Eiriksson

    SSC Guru

    Points: 182410

    Here is a quick and simple suggestion for a solution

    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;
    DECLARE @AD_STR VARCHAR(500) = 'CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk';
    DECLARE @PREFX VARCHAR(10) = 'CN=';
    DECLARE @DELIM CHAR(1) = ',';
    SELECT
    CHARINDEX(@PREFX,@AD_STR,1) AS WHERE_TO_START
    ,LEN(@PREFX) + CHARINDEX(@PREFX,@AD_STR,1) AS PREFIX_ENDS
    ,CHARINDEX(@DELIM,@AD_STR,(LEN(@PREFX) + CHARINDEX(@PREFX,@AD_STR,1) + 1)) AS NAME_END_POS
    ,SUBSTRING(@AD_STR,(LEN(@PREFX) + CHARINDEX(@PREFX,@AD_STR,1)),(CHARINDEX(@DELIM,@AD_STR,(LEN(@PREFX) + CHARINDEX(@PREFX,@AD_STR,1) + 1)) - (LEN(@PREFX) + CHARINDEX(@PREFX,@AD_STR,1)))) AS NAME_CHOPPED
    ;
  • jonathan.crawford

    SSCertifiable

    Points: 6408

    Or, if you wanted to go whole hog and just split everything out from your string, then access just the parts you need, you could use the DelimitedSplit8K approach a la Jeff Moden https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

    I left in all of Jeff's examples and Eirikur's example string, limiting to just the latter in the WHERE, but they are all there so you can see it working:

     --CROSS APPLY Usage Example:
    ---------------------------------------------------------------------------------------------------
    --===== Conditionally drop the test tables to make reruns easier for testing.
    -- (this is NOT a part of the solution)
    IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL
    DROP TABLE #JBMTest
    ;
    --===== Create and populate a test table on the fly (this is NOT a part of the solution).
    SELECT *
    INTO #JBMTest
    FROM (
    SELECT 1,'1,10,100,1000,10000,100000,1000000' UNION ALL
    SELECT 2,'2000000,200000,20000,2000,200,20,2' UNION ALL
    SELECT 3, 'This,is,a,test' UNION ALL
    SELECT 4, 'and so is this' UNION ALL
    SELECT 5, 'This, too (no pun intended)' UNION ALL
    SELECT 6, 'CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk'
    ) d (SomeID,SomeValue)
    ;
    GO
    --===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
    SELECT test.SomeID, split.ItemNumber, split.Item,splitAgain.ItemNumber,splitAgain.Item
    FROM #JBMTest test
    CROSS APPLY
    (
    SELECT ItemNumber, Item
    FROM dbo.FN_DelimitedSplit8k(test.SomeValue,',')
    ) split
    CROSS APPLY
    (
    SELECT ItemNumber, Item
    FROM dbo.FN_DelimitedSplit8k(split.Item,'=')
    ) splitAgain
    WHERE test.SomeID = 6
    AND split.ItemNumber = 1
    AND splitAgain.ItemNumber = 2
    ;

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • scdecade

    SSChasing Mays

    Points: 654

    Well in my opinion just looking for a comma is risky.  Trying to comma parse the entire string multiplies the risk!  So that seems unnecessary imo.  Here's a safer way that uses PATINDEX to search for any capitalized 2 letter combination encompassed between ',' and '=' and checks for no matches.  Eirikur layed it out nicely so I switched to his solution and modified

    DECLARE @AD_STR VARCHAR(500) =   'CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk';
    DECLARE @PREFX VARCHAR(10) = 'CN=';
    DECLARE @DELIM CHAR(14) = '%,[A-Z][A-Z]=%';
    DECLARE @START_AT int = LEN(@PREFX)+1;
    DECLARE @END_AT int = PATINDEX(@DELIM,@AD_STR collate Latin1_General_CS_AS)-@START_AT;

    SELECT IIF(@END_AT<=0, RIGHT(@AD_STR, LEN(@AD_STR)-LEN(@PREFX)), SUBSTRING(@AD_STR, @START_AT, @END_AT)) AS NAME_CHOPPED;

    • This reply was modified 1 month ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • DECLARE-NOOB

    SSC Enthusiast

    Points: 126

    Editing as there was loads of replies while I was typing (Sorry I'm actually at work so keep getting side tracked!)

  • This was removed by the editor as SPAM

  • DECLARE-NOOB

    SSC Enthusiast

    Points: 126

    Sorry guys my message got deleted as it thought I was spamming for some reason.

    Having a few issues with this I'm afraid but thanks for the input, basically the UADManager column for the @AD_STR variable cneeds to be referenced as the information changes in here, I have tried playing around with the variable but cannot get it to bind the column name when used.

    So taking the latest from SCDecade

    Capture

    uusername uadmanager

    Person a      CN=Person B,OU=Sales,OU=Company A - SK,OU=Companies - Company A,OU=Accounts - Companies,OU=Company A- Accounts,DC=DOMAIN,DC=co,DC=uk

    Person B      CN=Person C,OU=Sales,OU=Company A - SK,OU=Companies - Company A,OU=Accounts - Companies,OU=Company A - Accounts,DC=DOMAIN,DC=co,DC=uk

    Hope I have explained that correctly?

    Thanks

  • scdecade

    SSChasing Mays

    Points: 654

    drop function if exists dbo.test_name_chopper;
    go
    create function dbo.test_name_chopper(
    @PREFX VARCHAR(10),
    @DELIM VARCHAR(30),
    @AD_STR VARCHAR(500))
    returns table as
    return
    select
    IIF(PATINDEX(@DELIM,@AD_STR collate Latin1_General_CS_AS)<=0,
    RIGHT(@AD_STR, LEN(@AD_STR)-LEN(@PREFX)),
    SUBSTRING(@AD_STR, (LEN(@PREFX)+1), (PATINDEX(@DELIM,@AD_STR collate Latin1_General_CS_AS)-(LEN(@PREFX)+1)))) AS NAME_CHOPPED;
    go

    DECLARE
    @PREFX VARCHAR(10)='CN=',
    @DELIM VARCHAR(30)='%,[A-Z][A-Z]=%';

    with
    some_table_cte(name_to_be_chopped) as (
    select 'CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk'
    union all
    select 'CN=Some Other Person,XY=Human Resources,OU=Some Company - SK,OU=Companies - Some Company,OU=Accounts - Companies,OU=Some department,DC=somedomain DC=co,DC=uk')
    select
    tnc.*
    from
    some_table_cte stc
    cross apply
    dbo.test_name_chopper(@PREFX, @DELIM, stc.name_to_be_chopped) tnc;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply