Search code examples
sqlsql-serversubstringcharindex

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

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.

enter image description here

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

enter image description here

Solution

  • 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