Selecting Certain Part of a string

Question

Post reply

Selecting Certain Part of a string

DECLARE-NOOB

SSC Enthusiast

Points: 126
More actions
January 22, 2020 at 1:39 pm

#3717390

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

January 22, 2020 at 3:43 pm

This was removed by the editor as SPAM

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

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 1

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

declare @testnvarchar(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 More actions · Answer 2

Hi ScDecade thanks for the reply,

Thanks for your reply, just to check, if I declare the @test-2 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

Steve Collins SSCrazy Points: 2160 More actions · Answer 3

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 4 years, 10 months ago by Steve Collins.

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

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: 6612 More actions · Answer 5

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 6

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 4 years, 10 months ago by Steve Collins.

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

DECLARE-NOOB SSC Enthusiast Points: 126 More actions · Answer 7

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

This reply was modified 4 years, 10 months ago by DECLARE-NOOB.

DECLARE-NOOB SSC Enthusiast Points: 126 More actions · Answer 8

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

Steve Collins SSCrazy Points: 2160 More actions · Answer 9

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