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

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Mar 15 '23 at 20:31
There is no SQL Server **2018** version - we have 2012, 2014, 2016, 2017, 2019 and 2022 - so which one are you in fact using? — marc_s, Mar 15 '23 at 20:32
To be honest doing this with t-sql is painful. Sql server is just not good at this kind of string manipulation. This would be mountains easier to use CLR so you can easily parse the URL and the query string parameters. — Sean Lange, Mar 15 '23 at 20:39
See if this doesn't help you find a solution. https://stackoverflow.com/questions/8986150/parse-querystring-in-sql-server-2005 — Sean Lange, Mar 15 '23 at 20:44
`select *, substring(string, 1+nullif(patindex('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%', string), 0), 9), substring(string, 1+nullif(patindex('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%', string), 0), 6) from TableX` — lptr, Mar 15 '23 at 23:29

score 2 · Accepted Answer · answered Mar 15 '23 at 20:51

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

wish I would have refreshed my browser Patrick much more elegant solution — Ricky, Mar 16 '23 at 12:59

score 0 · Answer 2 · answered Mar 16 '23 at 12:52

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

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

2 Answers2