Return Integer from a string value with multiple variations, SQL Server 2018

Question

Trying to select account and principal ids from a string value as integers, the string varies in length and special characters, the value that identifies account can be accounts or AccountId - similar for principal, the account length should always be 6, and principal length should always be 9.

The data is stored as a string this is not a blob.

And I'm trying to extract the id values as integers from the string, output should look like this

Answer 1

Using the assumed DML/DDL:

SELECT *, TRY_CAST(CASE WHEN CHARINDEX('PrincipalID=',string) > 0 THEN SUBSTRING(string,CHARINDEX('PrincipalID=',string)+LEN('PrincipalID='),9) 
                        WHEN CHARINDEX('principals/',string) > 0 THEN SUBSTRING(string,CHARINDEX('principals/',string)+LEN('principals/'),9) 
                    END AS INT) AS PrincipalID, 
          TRY_CAST(CASE WHEN CHARINDEX('AccountID=',string) > 0 THEN SUBSTRING(string,CHARINDEX('AccountID=',string)+LEN('AccountID='),6) 
                        WHEN CHARINDEX('Accounts/',string) > 0 THEN SUBSTRING(string,CHARINDEX('Accounts/',string)+LEN('Accounts/'),6) 
                    END AS INT) AS AccountID
  FROM @Strings

Here we're using CHARINDEX, LEN and SUBSTRING to do string manipulation (which is probably better suited to another layer) to determine the string position of the four strings from the example, calculate their end position, grab the expected number of characters and then perform a TRY_CAST to confirm it's the correct data type.

ID	String	PrincipalID	AccountID
1	https://database.abcd.com/Pages/Principal Details.aspx?PrincipalID=123456789&	123456789	NULL
2	https://database.abcd.com/Pages/AccountDetails.aspx?AccountID=234567&	NULL	234567
3	https://database-db-account-docmgmt.abcd.com/accounts/123456/documents	NULL	123456
4	https://database.db.abcd.com/principals/987654321/home	987654321	NULL
5	https://database.abcd.com/Pages/AccountDetails.aspx?AccountID=654321&	NULL	654321
6	https://database.abcd.com/Pages/Principal Details.aspx?PrincipalID=234567890&AccountID=345678&	234567890	345678
7	https://database-db-account-docmgmt.abcd.com/accounts/345679/documents	NULL	345679

Answer 2

Here is what I came up with, created a temp table and case statements to define the string types and can select back to extract account id and principal id. Not very elegant but seems to work. Hope this helps someone, kind of don't want to see another charindex for a while.

IF OBJECT_ID('tempdb..#string') IS NOT NULL
    DROP TABLE ##string; 
  SELECT
    a.Id
    ,(CASE
    WHEN  a1_stop > a1_start
        THEN SUBSTRING(a.string,a1_start,a1_stop-a1_start+1)
    WHEN  a2_stop > a2_start
        THEN SUBSTRING(a.string,a2_start,a2_stop-a2_start+1)
    ELSE NULL
    END) as Account_Id
    ,(CASE
    WHEN  p1_stop > p1_start
        THEN SUBSTRING(a.string,p1_start,p1_stop-p1_start+1)
    WHEN  p2_stop > p2_start
        THEN SUBSTRING(a.rl,p2_start,p2_stop-p2_start+1)
    WHEN  p3_stop > p3_start
        THEN SUBSTRING(a.string,p3_start,p3_stop-p3_start+1)
    ELSE NULL
    END) as Principal_Id

    into #string

    FROM database.dbo.table as a

    LEFT JOIN
        (SELECT
        Id
        ,SUBSTRING(string,1,36) as string_type
        ,(CASE
            WHEN CHARINDEX('accounts',string) > 0
                THEN CHARINDEX('/',string,CHARINDEX('accounts',string))+1
            ELSE NULL
            END) as a1_start
        ,(CASE
            WHEN CHARINDEX('accounts',string) > 0
                THEN CHARINDEX('/',string,CHARINDEX('/',string,CHARINDEX('accounts',string))+1)-1
            ELSE NULL
            END) as a1_stop
        ,(CASE
            WHEN CHARINDEX('principals',string) > 0
                THEN CHARINDEX('/',string,CHARINDEX('principals',string))+1
            ELSE NULL
            END) as p1_start
        ,(CASE
            WHEN CHARINDEX('principals',string) > 0
                THEN CHARINDEX('/',string,CHARINDEX('/',string,CHARINDEX('principals',string))+1)-1
            ELSE NULL
            END) as p1_stop
        ,(CASE
            WHEN CHARINDEX('accountid',string) > 0
                THEN CHARINDEX('=',string,CHARINDEX('accountid',string))+1
            ELSE NULL
            END) as a2_start
        ,(CASE
            WHEN CHARINDEX('accountid',string) > 0 AND CHARINDEX('&',string) > 0
                THEN CHARINDEX('&',string,CHARINDEX('=',string,CHARINDEX('accountid',string))+1)-1
            WHEN CHARINDEX('accountid',string) > 0 AND CHARINDEX('&',string) = 0
                THEN LEN(string)
            ELSE NULL
            END) as a2_stop
        ,(CASE
            WHEN CHARINDEX('principalid',string) > 0
                THEN CHARINDEX('=',string,CHARINDEX('principalid',string))+1
            ELSE NULL
            END) as p2_start
        ,(CASE
            WHEN CHARINDEX('principalid',string) > 0 AND CHARINDEX('&',string) > 0
                THEN CHARINDEX('&',string,CHARINDEX('=',string,CHARINDEX('principalid',string))+1)-1
            WHEN CHARINDEX('principalid',string) > 0 AND CHARINDEX('&',string) = 0
                THEN LEN(string)
            ELSE NULL
            END) as p2_stop
        ,(CASE
            WHEN CHARINDEX('principal_id',string) > 0
                THEN CHARINDEX('=',string,CHARINDEX('principal_id',string))+1
            ELSE NULL
            END) as p3_start
        ,(CASE
            WHEN CHARINDEX('principal_id',string) > 0
                THEN LEN(string)
            ELSE NULL
            END) as p3_stop
        FROM database.dbo.table as table
        ) as b
    ON a.Id = b.Id;

    select id,
           account_id,
           principal_id
       
    from #database