Alrighty Then

How to Parse a string in SQl…not always easy…I imagine it really shouldn't have been difficult for M$ to make some T-SQL extensions, but they didn't (or at least up to 2k5…gotta look more into 2k8)

Even in REXX we had some nice features…like MASK, WORD, WORDS, etc.

So SQL Server Database devloper slaves are usually left with a myriad of slick hacks..REVERSE, CHARINDEX (to the nth degree), and ultimatley looping with logic in a User Defined Function (UDF)

In any case, we had an OP in this thread, "Parse String, Extract Multiple Parts of a Sting" ask how to parse out a string…so, I started my Daily "Practice"…then was tired of the same old thing.

Now, I don't know if anyone has done this before (I haven't seen it), but this access a table once, does no looping, eliminates the indecipherable n levels of CHARINDEX, and employees a series of nested derived tables.

Hope you like the hack

CREATE TABLE #myTable99(Col1 varchar(50))
GO

INSERT INTO #myTable99(Col1)
–                 1         2         3         4         5         6
–        123456789012345678901234567890123456789012345678901234567890
SELECT 'LDAP://adag.agaga,OU=audit,OU=NYC,OU=Paris,ad.fafsadfs,hjhkj' UNION ALL
SELECT 'LDAP://adagagaga,OU=finance,OU=LA,OU=London,adfafsadfs' UNION ALL
SELECT 'LDAP://adagagaga,OU=Marketing,OU=Dublin,adfaf.sadfs'
GO

SELECT      CASE
            WHEN Second_OU = 0 THEN NULL ELSE   
            SUBSTRING(Col1, (First_OU+3) ,(First_Comma-1) -(First_OU+3)+1) 
            END AS FirstValue
        , First_OU
        , First_Comma
        , CASE
            WHEN Second_OU = 0 THEN NULL ELSE
            SUBSTRING(Col1, (Second_OU+3),(Second_Comma-1)-(Second_OU+3)+1)
            END AS SecondValue         
        , Second_OU
        , Second_Comma
        , CASE
            WHEN Third_OU = 0 THEN NULL ELSE
            SUBSTRING(Col1, (Third_OU+3) ,(Third_Comma-1) -(Third_OU+3)+1) 
            END AS ThirdValue     
        , Third_OU
        , Third_Comma
  FROM (
    SELECT Col1
         , First_OU
         , First_Comma
         , Second_OU
         , Second_Comma
         , CHARINDEX('OU=',Col1, Second_Comma) AS Third_OU
         , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1, Second_Comma)) AS Third_Comma
      FROM (
        SELECT Col1
             , First_OU
             , First_Comma
             , CHARINDEX('OU=',Col1, First_Comma) AS Second_OU
             , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1, First_Comma)) AS Second_Comma
          FROM (SELECT Col1
                     , CHARINDEX('OU=',Col1) AS First_OU
                     , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1)) AS First_Comma
                  FROM #myTable99
                ) AS XXX
            ) AS YYY
    ) AS ZZZ
GO


DROP TABLE #myTable99
GO